Technical Question/ How to choose the lowest value from several fields in a record?

Hi there- I am new to Ninox and was wondering if anyone had any bright ideas- I basically want to choose the lowest of two fields, that are prices of the same item from different suppliers and return the product code? I apologise if this is blindlingly obvious!! Thers is an image below of an example record to helpexplain what i mean.

Alex

`if Price1 < Price2 then`

`  'Product Code 1'`

`else`

`  if Price 2 < Price1 then 'Product Code 2'`

`End`

`End`

Thank you Dean. It works fine for 2 or 3 if you nest teh if sttaements but what happens when you go to 4 or above?

Hi, you can use something like this:

`let m := min('Item price 1', 'Item price 2', 'Item price 3', 'Item price 4');if 'Item price 1' = m then'Supplier Code 1'elseif 'Item price 2' = m then'Supplier Code 2'elseif 'Item price 3' = m then 'Supplier Code 3' else 'Supplier Code 4' endendend`

you can always easily expand this formula with additional price and supplier fields.

Steven

That's really good- I thought it would be something like that but new to Ninox and don't know the syntax. thanks Steven!

Hi Steven- How would you get the formula to exclude £0.00 items? Many thks in adavance, Alex

Now I have to reach out to Sean because i thing it can be done with a regex expression and i'm not familiar with those.
What I would try is to make an array with zero excluded and from that array take the min().

something like this:

let rgex:= extractx('Item price 1', 'Item price 2', 'Item price 3', 'Item price 4', regex-formula);
let m := min(rgex);

if 'Item price 1' = m then
'Supplier Code 1'
else
if 'Item price 2' = m then
'Supplier Code 2'
else
if 'Item price 3' = m then 'Supplier Code 3' else 'Supplier Code 4' end
end
endif 'Item price 1' = m then
'Supplier Code 1'
else
if 'Item price 2' = m then
'Supplier Code 2'
else
if 'Item price 3' = m then 'Supplier Code 3' else 'Supplier Code 4' end
end
end

Steven

Did a copy/paste too much(Ipad heh), it should be like this:

let rgex:= extractx('Item price 1', 'Item price 2', 'Item price 3', 'Item price 4', regex-formula);
let m := min(rgex);

if 'Item price 1' = m then
'Supplier Code 1'
else
if 'Item price 2' = m then
'Supplier Code 2'
else
if 'Item price 3' = m then 'Supplier Code 3' else 'Supplier Code 4' end
end
end

Steven

Steven, thank you for the vote of confidence. Regex is use for matching patterns in strings or text so it's probably not the best tool for the job here. I would use the if-statements differently, something like this...

`let minPrice := 'Item Price 1';`
`if 'Item Price 2' < minPrice and 'Item Price 2' != 0 then`
`minPrice := 'Item Price 2'`
`end;`
`if 'Item Price 3' < minPrice and 'Item Price 3' != 0 then`
`minPrice := 'Item Price 3'`
`end;`
`if 'Item Price 4' < minPrice and 'Item Price 4' != 0 then`
`minPrice := 'Item Price 4'`
`end;`
`minPrice`

I think Supplier should be a separate table though. If that was the case you would use a single select statement instead of multiple if-statements.

I 👍 your approach. Thanks Sean.