0

Sum from multiplication of a fields with another field from different table

Hello,

I am not sure whether I have to open a new topic or reply from an existing similar one in here. But since the original post is already a long time ago, I decided to open a new one.

I am quite new in using Jet Report, so please bear with me on this one.

I would like to build a production report where I want to calculate the raw material volume needed in certain period in production order component and categorize the raw material by thickness and length.

Here's the Jet Formula that I used.

This is a filter formula for item categorization

I14 : =@NL("Filter";"Item";"No.";"Thickness (MM)";">="&G14&"<="&H14;"Length (CM)";$G$2)

This is a filter formula to get a date range.

N5 : =@NL("Filter";"Prod. Order Component";"Prod. Order No.";"Link=";"Production Order";"No.";"=Prod. Order No.";"Due Date";N4)

And this the formula that I used to calculate the total volume.

=@NL("Sum";"Prod. Order Component";"=NF(;""Expected Qty. (Base)"";""Prod. Order No."";"""&N$5&""";""Item No."";"""&$I14&""")*NL(;""Item"";""Unit Volume"";""No."";NF(;""Item No.""))")

 

When I enter that formula, I got a crash value on the excel cell, and when running the report, I got error message that informed me that the multiplication formula can't be evaluated.

Excel could not evaluate '=NF(;"Expected Qty. (Base)";"Prod. Order No.";"||"Filter","Prod. Order Component","Prod. Order No.","Link=","Production Order","No.","=Prod. Order No.","Due Date","01/09/2021..30/06/2022"";"Item No.";"||"Filter","Item","No.","Thickness (MM)",">=13,01<=21","Length (CM)","*"")*NL(;"Item";"Unit Volume";"No.";NF(;"Item No."))'.

 

Is there any missing formula that I am not aware of ? Any help in this matter would be greatly appreciated.

Best Regards,

Abu

4 comments

Please sign in to leave a comment.