Trouble getting related data from a value list entry

[Goetch]

Hi,

I am sure the answer is going to be one of those face palm moments, they usually are for me.... I have a simple file that I have attached. The file contains the following tables "Items", "Vendors", "Departments" and "Item Types" As you can see in the file I have related vendors --< Items >--Departments ---< Item Types . I made value lists for my departments, vendors, and item types based on the field data in those tables for my item entry screen. The department and vendor value lists work as I intended, however, I am having a slight problem with my item type value list.... I made it so that only the related item types will show based on the relationship with departments... Each item type has an item prefix. I made a simple calulation field calc sku, that says this Case (Dept="Furniture" ; vendors::Vendor Prefix&"-" & item types::itemPrefix&"-"&V. SKU ; item types::itemPrefix&"-"&V. SKU) The problem I am having is the calculation picking the correct item prefix. Any insight would be much appreciated..... I am sure I need another TO but I tried all different relationshsips and could not get everything to work the way I intended.

testfile.fp7 2.zip

[comment]

Could you explain what are you trying to achieve?

[Goetch]

Sure,

The actual file will be used to help the data entry people create new items for our pos system to import, as well as take vendor price lists and format them for import as well. The problem I am incurring right now is this; I have managed to create a conditional value list in the file that will only show related values of "item types" based on the department that has been picked. This seems to work great, however I also have a calculation field that I want to reference the item type's prefix, this does not seem to work, as it appears to only pick the first item type's prefix not the selected item types prefix....

I have tried changing the relationships around (I related the item types to the items table) but then the conditional value list does not work, and I have to list all values, but the calculation field works out fine, however I do not want a data entry person to be able to pick a item type from a different department.....

[comment]

If I understand correctly, you need to add another relationship between Items and (another occurrence of) ItemTypes, based on the type selected for the item. Use this relationship to get the type data to the item.

BTW, your relationships should be based on IDs instead of names. The way you have it now, renaming a department will break all its relationships.

[Goetch]

Comment as always it is a face-palm moment like I said.... I just was not thinking clearly, I changed the relationships to the id's and made another table occurrence, the only reason I was having problems was due to the fact I didn't use the ID's .... Thank you for your help, I think that this file looks better than the last one, again thank you for your input!! Is this file more relationally sound?

testfile.fp7.zip

[comment]

Yes. But the five Misc. fields are not. :mad: