wow...FM7 Lookup fantasy...wow

October 7, 200421 yr

Hey FM'ers.

Here's something I fall accross today, while studying some of the new Relationship concepts, and revisiting my current solution.

I didn't totally finalized it, but before the concept grows in complexity (as usual with my messy brain),here it goes.

Say, to give a concrete example, you have an Ordering db, where prices may be

- The Catalog Price

- A special net Price for given Customers

- A temporary Promotional Price

- A discount by Quantity Price

So I established separate TO's to be linked to the Line Items Table.

With v6, I used to test for valid relationships in the separate files (Promotions, SpecialNetPrices, Discounts, Catalog) and bring back by a script the correct discount rate, commentary and final price.

Now, here is the big move : LookUp (SourceField;result)

The price now can be brought to the lineitems through a single and simple PriceField, with an auto-enter calc of type :

LookUp(Promotionals::Price; LookUp(SpecialPrices::Price; LookUp(Discounts::Price;LookUp(Catalog::Price))))

In this situation, which is particular to my business case, I need to test the relationships in the sequence they are in the caculation.

Of course, I can overrule this auto-enter calc at the end if needed, but at least I have a "Here is what you should get" result.

The LookUpNext ( ) would switch to the Lower or Higher value in a single relationship. LookUp can automatically switch from a relationship to the other.

There are plenty of possible use of this new function, and this is probably the simpliest example I could use, which will surely end up being a Let ( ) functions for the parameters, with the "SourceField" being codified to be used dynamically, as I discovered it could be, involving GetField( ).

Well, this is the other part of the job.

This by itself assures data propagation in the right place, at the right moment, on your own needs.

Remember, if you can codify the function, not only would you be able to pass one field value from one table to the other, but any field you want (didn't tried it though, specially text -> num) by a quick switch.

Isn't that nice ?