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

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.

 

Many thanks in advance

 

Alex

Screenshot 2020-02-10 at 21.23.03

Ninox Profile

if Price1 < Price2 then

  'Product Code 1'

else

  if Price 2 < Price1 then 'Product Code 2'

End

End

M

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?Screenshot 2020-02-13 at 17.05.25

Ninox Profile

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'
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

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

Steven

M

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

M

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

Ninox Profile

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

Ninox Profile

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

Ninox Profile

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.

Ninox Profile

I 👍 your approach. Thanks Sean.

Reply