0

calculate stock

Hi all

I have a table "Fakturalinier Materialer" (Invoice lines articles) from which I want to control my stock. Therefore I made this calculation for a start, for later to set the stock value, but I am already "stocked" - sorry for the language.

for j in unique((select 'Fakturalinier Materialer').'Produkt nr.') do
    let x := number(sum(select 'Fakturalinier Materialer' where 'Produkt nr.' = j).Antal);
    x
end

 

I started testing with instead of having "Antal" I had "Produkt nr."

for j in unique((select 'Fakturalinier Materialer').'Produkt nr.') do
    let x := number(sum(select 'Fakturalinier Materialer' where 'Produkt nr.' = j).'Produkt nr.');
    x
end

and it worked excellent and gave me the 5 unique product numbers, however, when I changed it to the one which should be the basis for controlling the stock it just gave me 0,0,0,0,0

 

anyone have an idea of what I am doing wrong (the field "Antal" is a number field), and I have tried the formula both with and without the parameter number() ?

 

rgds

Leo

4 replies

null
    • Fred
    • 4 wk ago
    • Reported - view

    can you post a sample DB?

      • Leo_Woer
      • 4 wk ago
      • Reported - view

       Sure - enclosed - hope you can find the it (it is in Danish) - the formula I test it in is in the hidden tavle 'Fakturalinier Materialer'

      rgds

      Leo

      Time Sag.ninox
    • Fred
    • 4 wk ago
    • Reported - view

    My guess is it had something to do with where you put the sum() and number() commands.

    I put this in a formula field in Fakturalinier Materialer:

    let getAllRecs := (select 'Fakturalinier Materialer');
for j in unique(getAllRecs.'Produkt nr.') do
    sum(getAllRecs['Produkt nr.' = j].Antal)
end

    and I get:

    335,1600,26,900,10

    Since Antal is a number there is no need for the number() command.

    Your sum() command did not include the field name.

    sum(select 'Fakturalinier Materialer' where 'Produkt nr.' = j).Antal

    So you told Ninox to sum the records from the select. Then show the data in Antal for the sum of records. Which means nothing gets shown. Why it doesn't give you an error is beyond me.

    If you added one more set of parentheses then it will work.

    sum((select 'Fakturalinier Materialer' where 'Produkt nr.' = j).Antal)

    So now you are first doing a select with a filter then getting all the data from Antal then you are telling Ninox to sum all the data.

    Now to explain what I did. Selects should only be used when nothing else is possible and even then infrequently. So instead of doing two selects, I put the select into a variable then we can use that variable further down the code. Once you have an array of records, you don't use the where anymore and just the square brackets.

    • Leo_Woer
    • 4 wk ago
    • Reported - view

    Thank You very much - again superb service from your side. And especially thanks for letting me know when to use select - thanks again.

    regards

    Leo

Login to reply

Content aside

  • Status Answered
  • 4 wk agoLast active
  • 4Replies
  • 40Views
  • 2 Following