Normalized Design Help

[WarmBeer]

Hi All, Been away from FMP since v8. Just bought 10 Adv for a work project and getting used to the Relationship diagrams. Need some help on proper design for this project.

Incoming file has 500 lines, each line a value to define a set of insurance benefits (individual deductible $, family deductible $, Out of Pocket Max $, like that), let's call it the benefit file. Not all values are relevant on every file so I created a table where each line equals a record. That way I can have a self relation to view just the records that are relevant to that file or any other filtered set of values I want to view together. So far so good.

Now I need to create another table which will be used to fill a request form that is dependent on the values in the benefit file but with a variety of translations. So for example,

request form line(RFL) 4 = benefit form line(BFL) 33

RFL 22 = IF( BFL 6 > 23 & BFL 67 = "No", 20, 199)

and so on, essentially a set of rules which can get pretty complex.

What I am looking for is suggestions on how to structure the Request form table and the relationships so I can create the calc fields using the values from the Benefit File. I'm thinking I would like to have a record for each field on the Request Form and link the RF table to the BF table so I can reference multiple rows in the BF table to design my calculation for each row, but not quite sure how to set up that relationship or if maybe I am looking at this the wrong way all together.

Each BF has an ID so I can reference the set of records that correspond to a single inbound file by that id (it is stored in each record of the BF table). But if I create the relationship based on the ID and the line number of the inbound record I can only reference one line. This is where I get stuck.

Any help or suggestions greatly appreciated.

ed

[Søren Dyhr]

Interesting complexity indeed! What I would investigate is a way combine these three approaches:

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

http://www.fmforums.com/forum/showtopic.php?tid/195223/

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

...this should somewhat be the ingreediences, but what the actual measures they should come in, is a big IF...

--sd

[WarmBeer]

Thanks for trying to assist.

I was not able to get very far with the references you directed me to. I get stuck trying to refer to multiple entries in the BF table from the RF table. Not sure how any of these get me there.

What I got was this:

Ref#1 = X relation. I'm already doing something like this to display the filtered data elements.

Ref#2 = Long chain in that one. Was the point the Ugo method. Have to admit I did not completely follow that except it seems that the purpose is to reach through 1 relation to a 2nd. Still don't see how that gets me closer to 2 or more records from one relation, but perhaps if I understood the process better it would help.

Ref#3 = Again, this seems to be referring to the part I already have working, that is filtering the portal data relevant to a control.

Perhaps I was not very clear in the need. What I am trying to do is to reference more than one record in the related table in order to perform a calculation and get a result for the current record. That calculation will use different combinations of records from the related table. As I see it the only way is to either:

1. Have an extra field in the parent table for each possible value I want to use from the child and use a separate relationship to capture each value. This works unless I have to use many values from the child to calculate the parent for even just one parent.

2. Duplicate the data so that the child table for calculations has 1 record with 500 fields and a separate child table is used for displaying & filtering the benefit data where each data element of the benefit file is a record.

I was looking for #3 where somehow through a single child table I could both display filtered elements of the Benefit AND use different combinations of Benefit elements to calculate a value in each of the rows in the Request/Parent table.

[comment]

It's not easy to single out one child record out of many, but it should be possible. Unfortunately, your explanations do not make it clear what exactly should be used to identify such record:

IF( BFL 6 > 23 ...

What does the "6" represent here? Is it a field value in the child table, and if so - can we be sure it is unique to each parent? Will there always be a fixed pattern of child records per parent?

[Søren Dyhr]

My interpretation of this is the line number, but it's already said that each line is a record, so it seems be very close to:

http://fmhelp.filemaker.com/fmphelp_10/en/html/func_ref3.33.10.html

But one big problem here is if a foreign key is dependent on something stored in other records will it not work, it is the unstored nature of such value not allowing other methods than Ugo's to create new records on this filtering approach. It has to be an Ugo key which will or will not contain the records ID to provide further linking via an indeed ...selfjoin.

A portal will be filled with a strained set of records, so only those will allow creation of new records in the entry table.

--sd

[WarmBeer]

Yes Soren, you are correct in that BFL is Benefit File LINE, so in comments example I am comparing line 6 of the form to line 23, each of which are in their own record.

Your new reference is helpful, but I think you are saying it will give me a problem. Not sure I fully understand the problem. It sounds like one of stability. It looks like FM now allows you to change the sort order of a portal separate from the sort order of the relationship. Is that true? If I have a sort order in the relationship and then set a different sort order for the portal does it override the sort for just that portal or overwrite the sort order of the relationship. And which sort order does the GetNthRecord reference, relation or portal.

Also, trying to understand the Ugo method. Is the concept that I use a calculation field in the parent record as part of my Key in order to filter the related records dependent on selecting a different parent record? If so I do not think it will be helpful unless I move the resulting value to a more stable field with a script. I need to export the results from the 300 RFL records in order to populate my result, which is a form to send to another department.

I will explore these ideas a bit further and hopefully get a better understanding.

thanks

ed

[Søren Dyhr]

And which sort order does the GetNthRecord reference, relation or portal.

What is your guess? The first, an in this case must it be identical with the creation order in a carthesian selfjoin - sans sort!

Is the concept that I use a calculation field in the parent record as part of my Key in order to filter the related records dependent on selecting a different parent record?

If a calc is true, is the records own ID used to another linking of the same TO, to provide the query - so bingo if thats what you're saying!

You might consider:

http://fmhelp.filemaker.com/fmphelp_10/en/html/func_ref3.33.4.html

But by and large should it be possible to make records disappear when preemtive information renders them irrelevant, provided it happens in a portal showing the rightmost table in my image. When all entry is completed, is it a simple GTRR to the rightmost that yields only the relevant record as their relevant questions tunneled in via the relation.

--sd