Relationship/Table help needed

[JD2775]

Hi guys,

I have 3 tables I am working with in my Database:. tbl_Location, tbl_Warehouse and tbl_Issue, related to each other.

tbl_Location to tbl_Warehouse (one to many) and tbl_Location to tbl_Issue (one to many)

Depending on what locaiton the user enters a dropdown value list displays of all the Warehouses for that location (anywhere from 50-75). Also depending on the location that is chosen a drop down value list appears of common issues to that location.

I need to add a field "Section". The way this would work is depending on what Warehouse AND Issue the user chooses (combination of both) a set value list would appear for section. There could be any number of sections for each Warehouse/Issue combination.

How should I approach this? I am thinking another table is needed, but I still can't visualize how I would populate Section based off 2 fields of data.

Thanks

[doughemi]

Please describe the process the user follows. I can't visualize a location that has several warehouses, and can't figure out what an issue or a section is and how they should relate to the others.

[JD2775]

Hi Doug,

Location examples: CA, NV, AZ

within each of those locations are a set list of Warehouses.

CA

Warehouse 1

Warehouse 2

Warehouse 3

NV

Warehouse 4

Warehouse 5

Warehouse 6

The process is the following:

The user records maintenance issues on a specific warehouse on an iPad (FM GO app). If the user chooses CA from the location, only the Warehouses associated with CA appear in the drop-down menu. From there you have set Issues associated with a Location. CA has issue1, issue2, issue3 NV has issue4, issue5, issue6....

The field I need added is for Section. The way this would populate is based off the Warehouse and Issue that are chosen. Warehouse1/Issue2 would have a specific dropdown list associated with it.

Does that help?

The user

[doughemi]

I can't see why you have a separate table of locations. A Warehouse has one and only one location; therefore location should be a field in tbl_Warehouse. The value list for your dropdown would then be locationVL, consisting of values from tbl_Warehouses::location. tbl_Issues would remain. Since one or more issues would apply to one or more warehouses, you would require a join table between tbl_Warehouses and tbl_Issues, tbl_WH_ISS. It would contain fields for warehouse id, issue id, and other details (report date, resolution date, resolution, etc). Each record would record one instance of one issue in one warehouse.

You still haven't explained what a section is or does, so I can't determine if it should be a separate table, or an attribute of tbl_WH_ISS. You also haven't explained what information needs to be reported, so I can't tell where the LocationFind global field (where the end user enters his search criteria) should be located.

[comment]

If the user chooses CA from the location, only the Warehouses associated with CA appear in the drop-down menu.

This is called a conditional value list. There are many examples on the forums, e.g.:

http://fmforums.com/forum/showpost.php?post/269504/

http://fmforums.com/forum/showpost.php?post/346499/

I can't see why you have a separate table of locations.

Where else would you keep them?

[doughemi]

Where else would you keep them?

A Warehouse has one and only one location; therefore location should be a field in tbl_Warehouse.

Seems to me location is an attribute of a warehouse. What am I missing?

[JD2775]

Im sorry Doug, you are absolutely correct. I was mistaken as I just double checked. The locations are actually a value list, not a table, consisting of values of tbl_Warehouses::location just as you mentioned.

A section would be populated based off the Warehouse/Issue that is checked. Warehouse 1/ Issue "Exhaust Fans" would have a related value list of Exhaust Fan 1, Exhaust Fan 2 to choose from. Warehouse 2/ "Exhaust Fans" would have 4 Exhaust Locations (bigger warehouse). Hence the need for Section to fill based off combination of Warehouse/Issue that the user selects. Currently it is just a free text field, I'd like to actually have set choices to pick from,

Hopefully I am starting to make more sense.

Thanks

This is called a conditional value list. There are many examples on the forums, e.g.:

http://fmforums.com/...hp?post/269504/

http://fmforums.com/...hp?post/346499/

I got that part Comment, the conditional value list is is already set for that. I need a value list to populate off data from 2 fields that are chosen. Would that still entail a conditional value list?

Where else would you keep them?

[comment]

I need a value list to populate off data from 2 fields that are chosen. Would that still entail a conditional value list?

Yes, it's the same thing, except the relationship used to determine the "related values" needs to be based on matching both fields.

Seems to me location is an attribute of a warehouse. What am I missing?

Well, a ParentID is an attribute of Child. That doesn't preclude the existence of a table of Parents.

When the actual locations in use are trivial such as US state abbreviations (i.e. they are unique, not likely to be renamed and have no attributes of their own) you can get away with using a value list instead of a table. But that is the exception, not the rule.

[JD2775]

Thank you Comment. So it sounds like I need an additonal section table, tblSection linked to tblWarehouse? From there do the conditional value list based off tblIssues::Warehouse and tblSection::Warehouse? Or am I mixed up there