0

Formula to pull price from matching price sheet

I have the following structure

(- indicates child)

Customers

Orders

- Line Items

Products

Price Sheets

- Pricing

 

In the pricing table, I have a record per product, per price sheet (distributor, wholesale, retail)

A customer is linked to 1 price sheet

Line items is linked to products. When I create an order, I assign a customer. I then assign line items. When I assign a line item, I want ninox to check the product code I'm using, then check the customers linked price sheet for that product, and return the price.

 

Can't figure out the formula though. Its driving me a bit batty. Any suggestions?

4 replies

null
    • SECOS Group
    • quartz_cap
    • 5 yrs ago
    • Reported - view

    I do believe I just figured it out! I think. It seems to be working. But, curious to know if theres a better way to achieve this:

     

    let PL := '3. Orders'.Customer.Pricing.'Pricing Level';
    let PS := Products.SKU;
    sum(Products.Pricing['Pricing Level' = PL and SKU.SKU = PS].'AU$/Bag (ex GST)')

    • SECOS Group
    • quartz_cap
    • 5 yrs ago
    • Reported - view

    So this code was working up until I made a slight change in the pricing table. Now its not working at all when I use the if statements - if I use the above code it works fine.

     

    let PL := '3. Orders'.Customer.Pricing.'Pricing Level';
    let PS := Products.SKU;
    if Unit = 1 then
    sum(Products.Pricing['Pricing Level' = PL and SKU.SKU = PS].'AU$/Bag (ex GST)')
    else
    if Unit = 2 then
    sum(Products.Pricing['Pricing Level' = PL and SKU.SKU = PS].'AU$/Pack (ex GST)')
    else
    if Unit = 3 then
    sum(Products.Pricing['Pricing Level' = PL and SKU.SKU = PS].'AU$/Carton (ex GST)')
    end
    end
    end

    • SECOS Group
    • quartz_cap
    • 5 yrs ago
    • Reported - view

    And I managed to fix it - I had some other formulas that were modified by the change I made, so I had to go back and edit them. All working perfectly now : D

    • Nick
    • 5 yrs ago
    • Reported - view

    Good job Sarah! Thanks for sharing.

Login to reply

Content aside

  • 5 yrs agoLast active
  • 4Replies
  • 1300Views