Lookup relationship

Questions
1

I'm using a relationship to fill the estimate lines with the proper price for a given product in function of the qty of product ordered.

It works well. Except that I need to find a way to control the "infinite" concept.
Initially I was using -1 as volume treshold for price codes that don't have upper limits e.g. a product which would have only a regular price, or the top volume level e.g. regular 1-10, vol1 11-20, vol2 21+

Unfortunately, that is interpreted as smaller than the first threshold so it make it the first threshold and bumps all others.

I thought of having an auto-calc that would convert "" into 10 000 000 which seems reasonnably high. But somehow, the autocalc won't fill the field with it.
I could also have a button that the client can press to have it set the field to ten millions but that's not really "modern" lol.

relationship
relationship699×441 30.4 KB

> This is the relationship.
> And below, the field definition for threshold which doesn't replace with 10millions if empty.

field%20definition
field%20definition1167×209 18.4 KB

Below you can see that the smallest quantity in the Teal treshold should have the higher price and the top quantity (pink) should fall above the last treshold set (volume2) and have the smallest price. In other words, the pink square on the left should be at the bottom but because it is 0 instead of infinite, it bumps the other volumes down.

should%20be
should%20be1090×605 72.4 KB

2

Hi @Cecile, you can play with LookupNext() and see if that takes care of your problem: FileMaker Pro 18 Advanced Help

I’m linking to the FM18 help, but the function exists since FM7.

Alternatively, I would try making sure your auto-enter with the case statement is set to evaluate even if all referenced fields are empty. Using isEmpty(Self) instead of Self = "" usually takes care of something like that for you.

Let me know if this is helpful or not.

3

IsEmpty… I like IsEmpty… Dunno why I didn’t think of using it there! Thanks. I will look into lookupNext as well :wink:

4

Using Let (), with Max() and Min() would allow you to generate sensible data. You could define at arbitrary value as Max() plus a constant value.

1 Like
5

Not sure I’m clear how that will script but I will try.
Along these lines, I had thought of doing a floor and ceiling threasholds instead of just one. where < Qty <