I have the following tables



Products_table

Product_id

Category_id

Subcategory_id



Categories_table

Category_id

Category_name



SubCategories_table

SubCategory_id

Category_id

SubCategory_name



And these Relationships



Products_table::Category_id = Categories_table::Category_id



Products_table::Subcategory_id = SubCategories_table::SubCategory_id



Categories_table::Category_id = SubCategories_table::Category_id



There are SubCategories for eatch Category

e.g Category 1 has SubCategories 1,2,3,...

Category 2 has SubCategories 1,2,3,...



The user selects the Category of the product and the SubCategory depending on the chosen Category.



I wand to find a way to prevent deleting a SubCategory when there are products belonging to that Category and SubCategory. In the beginning It.looks easy but I realized that in FM8.5 you can not name a relationship (like in FM.6.0) and you cannot reference an other file through a given relationship.



Any help will be appreciated



Nikos

"Hatzigiannakis Nikos" <nikos (AT) ypai (DOT) gr> schreef in bericht news:eualc4$a8v$1 (AT) volcano1 (DOT) grnet.gr...
I have the following tables



Products_table

Product_id

Category_id

Subcategory_id



Categories_table

Category_id

Category_name



SubCategories_table

SubCategory_id

Category_id

SubCategory_name



And these Relationships



Products_table::Category_id = Categories_table::Category_id



Products_table::Subcategory_id = SubCategories_table::SubCategory_id



Categories_table::Category_id = SubCategories_table::Category_id



There are SubCategories for eatch Category

e.g Category 1 has SubCategories 1,2,3,...

Category 2 has SubCategories 1,2,3,...



The user selects the Category of the product and the SubCategory depending on the chosen Category.



I wand to find a way to prevent deleting a SubCategory when there are products belonging to that Category and SubCategory. In the beginning It.looks easy but I realized that in FM8.5 you can not name a relationship (like in FM.6.0) and you cannot reference an other file through a given relationship.



Any help will be appreciated



Nikos

In article <eualc4$a8v$1 (AT) volcano1 (DOT) grnet.gr>,
"Hatzigiannakis Nikos" <nikos (AT) ypai (DOT) gr> wrote:

I don't really understand your last statement. However, I offer the
following idea:

You can define a calc field in Categories (and a similar field in
Subcategories), that counts the number of related Product records. You
can write a script to delete a record, with an If() statement based on
the condition that the count of related records is greater then zero,
which will cause a suitable message and then exit the script. You would
then limit the menu set for the user so that the user can not simply use
the Delete Record command in the menu bar, but would have to use the
script to delete a record.

--
For email, change <fake> to <earthlink>
Bill Collins

"Hatzigiannakis Nikos" <nikos (AT) ypai (DOT) gr> schreef in bericht news:eualc4$a8v$1 (AT) volcano1 (DOT) grnet.gr...
I have the following tables



Products_table

Product_id

Category_id

Subcategory_id



Categories_table

Category_id

Category_name



SubCategories_table

SubCategory_id

Category_id

SubCategory_name



And these Relationships



Products_table::Category_id = Categories_table::Category_id



Products_table::Subcategory_id = SubCategories_table::SubCategory_id



Categories_table::Category_id = SubCategories_table::Category_id



There are SubCategories for eatch Category

e.g Category 1 has SubCategories 1,2,3,...

Category 2 has SubCategories 1,2,3,...



The user selects the Category of the product and the SubCategory depending on the chosen Category.



I wand to find a way to prevent deleting a SubCategory when there are products belonging to that Category and SubCategory. In the beginning It.looks easy but I realized that in FM8.5 you can not name a relationship (like in FM.6.0) and you cannot reference an other file through a given relationship.



Any help will be appreciated



Nikos



Since you already reference by ID, you just make the fields invisible where you set these. You guide the user to a seperate layout where the ID is set. Click a button and turn to the main layout. When the field has a value the user can not return to this layout and thus can't change the contents of the field for your relation.



Further, there are no relationships as in FM6 any more. We now call them TableOccurrance. (TO). You can give a TO any name you want.



If you need to reference a file for a second time you can do it by creating a second TO to this table (or file) and give it a meaningfull name.



Now about deleting a sub-caregory when there is still a product that uses this. That can be set up with v8.5 adv, but not with v8.5. Any user can always use ctrl-E to delete a record. The only way to prevent this is to give the user NO access to the table that holds the sub-categories. If you want the user to add sub-categories, you can build a table with only one record, holding just one global field. On the layout also the field for the related category. The user enters the new sub-category in the global field, presses a button and a new record is made in the sub-category table and the value is transported there. It takes some more work then I have discribed here, but it should not be too diffucult to set up.



Keep well, Ursus

Thanks a lot.

Is it possible for a script to check if a given subcategory has at least one related recond in the products table and how?

Nikos Hatzigiannakis" <nikos (AT) khy (DOT) gr> schreef in bericht news:eujhpl$9mj$1 (AT) volcano1 (DOT) grnet.gr...

Thanks a lot.
Is it possible for a script to check if a given subcategory has at least one related recond in the products table and how?

====================

Nikos.
You can check this in more ways.

Use the Count funtion, or use the IsValid function

With count you can count how many times an item is still in use, returning Empty when there are no items

script something like:

if not isempty ( count ( MainTable::IDfield ; RelatedTable::IDfield ) )
you can safely delete the related record.

OR you can see if the current relation is not valid (There are no records attached)

if not IsValid ( MainTable::IDfield ; RelatedTable::IDfield )
you can safely delete the related record.

But this will not prohibit the user from going into the related table and manually deleting the record.

Ursus

That is exactly what I want to do. But the functions IsEmpty and IsValid, dont have two parameters (first one the master file and second the ralated) but only one the related table field asuming that you are using the curent layout relation. This is not my case. I want to have a layout of a table acurance and check if an other relation (of another table ocurance) exists.

Please look at this example with 3 tables

Protucts_table Categorytable Subcategory table
Category_ID ---------------> CategoruID -------> CategoryID
SubCategoryID SubCategoryID

And relations

1. Products_table::Category_id = Categories_table::Categoryid


2. Categories_table::Category_id = SubCategories_table::Category_id



There is also a relationship

3. Products_table::Category_id+SubCategoryID = SubCategories_table::Categoryid_SubCategoryID



I want to diaplay records from the category table with a portal list of subcategory table using relation 2. But I want to check if I can delete a portal row of subcategories table using relation 3. !!!!!!



Please Help!!!!