[Cross Post] Complex Lookup

[steveald]

I am building a database that includes one table for lookup data and another table acting as the user interface, containing all of the calculations and scripts. The lookup table contains 304 records where the primary field can be one of 38 different values and 3 other fields can each be one of 2 different values. In other words, there are 8 records for each of the 38 primary values depending on which option is listed for the other 3 fields. Each record then contains 12 fields of lookup data I need to be able to lookup from the main UI table. 

Two of the 3 fields in the lookup table are related to fields in the UI table since their values can be changed by the user. I can easily set up lookup fields in the UI table for each of the 12 values. But, since only 2 fields are related, it only pulls data from 4 of the records in the lookup table. I need to be able to automatically perform lookups where the criteria includes one of the 38 primary field values and one of the 2 options on the non-related field.

That means 912 lookup fields in the UI table (12 lookups for each of the 38 primary field options and each of the 2 non-related field options). But I don't know how to set up the UI table's lookup field to check each lookup table record for a specific primary field value and non-related field value. I tried an If () calculation, but it only looks at the 2 related fields.

It's entirely possible I am not looking at this the right way. So I am open to any suggestions that may require reconfiguring the lookup table or anything else. Please let me know if I did not explain it clearly enough. Perhaps a screenshot or two will be necessary. Thank you in advance for any direction you might be able to provide.

Edited August 6, 2019 by steveald

[Wim Decorte]

Let's get some of the lingo straight:

a table is something to store data in (records and fields)

a FM file can hold up to 1,000,000 tables.  And it also holds layouts and scripts.  But a table cannot hold UI or scripts.  A database is somewhat of a generic term sometimes narrowly used to define the set of one or more tables, and sometimes more broadly used to determine one or more FM files (so not just the tables but also the scripting and the presentation layer).

In your case: do you have two files, one for the data and one for UI and scripting?  Basically following the data separation model?

I think you need to describe your model in some detail, because it is pretty much a given that you do NOT need 912 fields.

[steveald]

Okay, some more details.

The first screenshot is from my database file, showing (among others) the two tables I referred to above - the first being what I referred to as the UI table and the last (CI Rates) being the lookup table. Users only see/have access to layouts from the first table.

The second screenshot is a sample of a layout from that lookup table showing all 8 records containing the Code "WESC", as well as the related fields Base and Enh, the non-related field Gen and the 12 lookup fields that will be sent to the users table. The 12 fields represent minimum ages.

The way it needs to work is this: Once Base and Enh values are selected by the user, then - for each Code and Gen entry - the database needs to lookup the appropriate value for one of the 12 age fields. For example, if Base is set to "AA" and Enh is set to "Y," then one lookup that needs to be done would be for Code = "WESC" and Gen = "M" and minimum age = 55. The correct value that needs to be returned from the set above in this case is 352.1616.

Another lookup would also need to be done for Gen = "F". And each of those would need to be done for the other 11 minimum ages. And each of those would need to be done for the other 37 Code values.

So, you are correct, I don't need 912 fields. That would only cover one of the two Gen options. I need 1,824 values returned - if not as fields, then something else that can be input into some fairly complex calculations.

The user exerience is this: They make a handful of selections including those for Base and Enh. Lookups are done behind the scenes based on those two selections. The lookups are applied to complex calculations that include values determined by other selections that were made. One result is calculated for each of the 38 Code items and those results are added together. This is done for each of the two Gen entries. The net result is the user sees two rows (one for each Gen) of numbers for the 12 max ages. 

Hopefully that explains where I am coming from. Again, if I need to look at this from a very different angle, I am open to suggestions.

Edited August 5, 2019 by steveald

[OlgerDiekstra]

Or 577 in one table. That's a good indicator the design needs improvement.

[steveald]

Or, that it is a large and complex solution.

With a limit of 256 million total fields per record, don't underestimate the capabilities of FileMaker.

[steveald]

If it helps at all, my thought process thus far has been...

Normally, after setting up the necessary relationships between fields in the main and lookup tables, I would configure a field as a Looked-up value that would pull the appropriate value from the first matching related record. Unfortunately, only 2 of the 4 required data points can have relationships set up between the two tables. I don't know of any way to add conditions to the Looked-up value option.

Alternatively, I thought about configuring the field as a calculation centered around the Lookup () function, but I haven't figured out a way to incorporate the 2 non-related values into the calculation either. A Lookup () alone, of course, finds the first matching instance of the 2 related fields - so it never looks past the first 4 records in the lookup table. Adding conditions to the Lookup () function (If (), Case (), etc.) result in nothing being returned.

The obvious answer, it would seem, would be to set up fields in the main table for the 2 non-related data points and create relationships between them and their corresponding fields in the lookup table. But, one of them (Code) would need to be any one of 38 different values at the same time and the other (Gen) would need to be either of 2 values at once.

I somehow need to be able to look up 76 unique values (38 Code values and 2 Gen values) based on how the 2 related fields are set so that those values can be used in the calculations described above. One of those lookups could be:

If Base = "IA" and Enh = "N," then the min age "55" lookup value for Code = "WESC" and Gen = "F" would be 349.2198.