trying to avoid bandaid fixes

[flyf]

Currently I need have two tables and one that needs to be created:

1. Inventory of all items

a. _kp_UniqueID

2. Orders (a bit more complicated than that but for simplicity sake) - allows us to order each item many times and gives each order a separate barcode which is tracked

a. _kf_ParentUniqueID (relationship with Table1)

b. _kp_OrderID

© _kf_NonInventoryUniqueID (will be relationship with Table3)

(3) Non Inventory

a. _kp_NIUniqueID

I need to add another "non inventory" table (3) in now that will also need to have orders created. Our IT department and many projects extract data from our FileMaker program and are reliant on the _kp_OrderID that is created so we have to filter all this data to the ORDER TABLE (2) as well.

Requirements:

A. I need to be able to relate the non-inventory (3) table to the Inventory Table (1) in case items from (3) become Inventory items (1) which I will do by using the _kp_UniqueID relationship.

B. Currently I only display the Inventory Fields (1) in the Order layout (2) so that I do not duplicate data or if we make changes to the inventory name then it will be uniformly updated. This also prints out on a label.

C. PROBLEM: Clogging up the layout with displayed fields

Ex. Now I will have display fields for Name (Table1); Name (Table3); ItemNumber (Table1); ItemNumber (Table2); etc..... AND I will need to create an additional label layout.

D. I am hoping someone can give me a suggestion on how to have "either/or" fields in Order Table (2) so that if the relationship is with Table 1 that it will fill in the Name from Table 1 and if the relationship is with Table 3 that it will fill in the name from Table 3, etc and then I will also only need 1 label format.

The reason I cannot combine Table 1 and 3 are a bit complicated because I used more generic terms for ease but I cannot combine these tables even though it would obviously make it much easier

I am sure that I can do this by having a cluttered orders layout and having multiple layouts for the label but I would love to avoid this if I can and then in the future I can use this technique to avoid bandaid fixes each time this sort of things occurs.

Please let me know if that was not clear enough. Thanks so much in advance for any suggestions you can give me.

[Fitch]

I'm not totally clear on your data model here, but lately I've been using filtered portals to solve problems that I think are similar. I.e., a related field inside a one-line transparent filtered portal. Or hidden tab objects.

[LaRetta]

It would help to see a sample file of your structure with a few records but I'm inclined to question why you have two tables (one for inventory and one for non-inventory) instead of a flag field IsInventory in a single table? Part of your problem stems from that very separation.

ADDED: You can consider using a single Inventory table with only some of those unique or unusual fields as child table (even 1:1) but it also doesn't hurt to have extra unused fields in a table. It's hard to say without more information about why you chose to split them. We might be able to help you keep them together.

Edited April 27, 2012 by LaRetta