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
-
can you post a sample DB?
-
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.
-
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
Content aside
- Status Answered
- 1 mth agoLast active
- 4Replies
- 40Views
-
2
Following