Help with formula (MIN) with conditions

[hera]

Hi, for my document where I list my investments in stocks and check possible new investments I have listed my actual and past investments in the widget A (“Aangekocht en/of verkochte aandelen”).
In the widget BC (“Transacties”) all transactions have been listed.

So in the field “Huidig” (= current) of widget A the actual rate of the stocks are listed. In field “Tarief” the rate is listed what I actually bought or sold the stocks for. In the field B I have listed the received dividend.

My problem is that in the field “Aankoop” (bought) I have the formula MIN(Transacties.lookupRecords(Bedrijf=$id).Tarief). I want this changed because this should only be used for fields in which “Aantal” > 0. MIN(Transacties.lookupRecords(Bedrijf=$id, Aantal >0).Tarief) gives an error, who can help me?

[jarek]

lookupRecords doesn’t support conditions (like Column<23). You first need to add a formula Column, with that condition precalculated (like new column "IsGreaterThen23), to the target table, and then use that column in the lookup method.

[hera]

Thanks it worked,

I added a field named “Aankoop” (if $Soort == “Aandelen” and $Aantal > 0:return True else: return False)
And changed the formula in MIN(Transacties.lookupRecords(Bedrijf=$id,Aankoop=True).Tarief)

Now my page is as follow: