0

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.

 

Many thanks in advance

 

Alex

Screenshot 2020-02-10 at 21.23.03

9 replies

null
    • Choices_Software_Dean
    • 4 yrs ago
    • Reported - view

    if Price1 < Price2 then

      'Product Code 1'

    else

      if Price 2 < Price1 then 'Product Code 2'

    End

    End

    • Hinpack Limited
    • Alex_B
    • 4 yrs ago
    • Reported - view

    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 partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    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

    • Hinpack Limited
    • Alex_B
    • 4 yrs ago
    • Reported - view

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

    • Hinpack Limited
    • Alex_B
    • 4 yrs ago
    • Reported - view

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

    • Ninox partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    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 partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    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

    • Sean
    • 4 yrs ago
    • Reported - view

    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 partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    I 👍 your approach. Thanks Sean.

Login to reply

Content aside

  • 4 yrs agoLast active
  • 9Replies
  • 1515Views