0

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

Abu Tolib Fakhroji

June 27, 2022 07:08

None

Follow

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

• Facebook
• Twitter
• LinkedIn

4 comments

• 1
  
  CascadiaDataworks July 07, 2022 20:38

  There is a lot to test and validate within your question, which might be why there wasn't a response. 

  First, I suggest testing each of the NL("Filter") to ensure they are returning data as expected.  The easiest way is to change "Filter" to "Rows" and see what is returned. I usually would make a copy of my report, then delete everything I don't need to test each Filter to Row test to ensure no other errors occur.  Also saves time in running.

  Next, I would test the NL("Sum") by removing the multiplier from the Item table.  Does this return values?

  If all of the above are working, it would probably be a good idea to connect with a consultant to be able to review it live and try to troubleshoot.

• 0
  
  Abu Tolib Fakhroji July 13, 2022 05:57

  Hi Bob,

  Thank you for your feedback and input to try and test all the NL("Filter") functions, and sorry for the long response from my side.

  After trying out the functions, I finally got where the issue is, which is in the thickness filter using cell reference below.

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

  The value in the cell itself is a decimal number (i.e. G14 : 10.01 H14 : 15), and the reason I am using a cell reference is because I will have a lot of decimal ranges to categorize, so it's easier to manage, also if I would like to change the categorization in the future.

  After trying out many formula, including using two dots to indicate range (G14..H14), I still get #VALUE result. Trying out NP Integers function doesn't give me #VALUE, but it doesn't get me the result I am looking for, since it only returns integers number.

  When I tried to input the actual decimal number inside the filter (10.01..15) instead of using cell reference, I can get an actual value in my NL Rows with data that I am looking for. This raise another question from me. Does Jet have a specific syntax where it can get a range of Decimal numbers using cell reference ?

   

  Best Regards,

  Abu

  Edited by Abu Tolib Fakhroji July 13, 2022 05:59
• 1
  
  CascadiaDataworks July 13, 2022 22:13

  It almost seems like your values in G14 and H14 are text, not numbers.  That would definitely cause an issue compared to hardcoding the values in the function.

  It also sounds like you want to have it as a changeable Report Option, which means you will need to re-run the report each time you want to change the high/low values.

  Why not have a single cell where you enter the range you want?  When using a range of values, it is better to use the format ##..## rather than greater than and less than.  In your case, G14 would just be 10.01..15

  Without seeing the report structure, I'm not sure if this solution could work for you but that should resolve the issue. 

  If they have to be references to those two cells, you can try replacing your formula with something like $G$14&".."&$H$14 to build out the ##..## format.

• 0
  
  Abu Tolib Fakhroji July 15, 2022 02:58

  Hi Bob,

  Thank you for the insight. It definitely helps me a lot!

  The 2 cells (G14 and H14), I have made it to number, by cell format or using text to column function, but the result is the same, but the core issue also might be because of regional setting, I actually use (,) as decimal, but Jet only consider (.) as decimal despite my regional setting, which complicates things.

  Even though I tried to hardcode the variables into the jet formula, 10,01..15 gives me #VALUE while hardcoding 10.01..15 gives the desired results. I've tried to type cell G14 to be 10.1 (excel consider this as text), and 10,1 (excel consider this as number), and then in the jet formula, I input $G14&".."&$H$14 using both scenario above, but they still give me #VALUE.

  The reason why I don't use the range in a single cell are because I will have many number range, and each range, I am using variable to calculate the range, 15 in cell H14 is actually calculation of G14+4.99, and I keep the value of 4.99 in another cell, which is going to be a parameter in report options.

  But your point make sense to make the range in a single cell, I am currently making 2 more columns, I14 to convert number in G14 with (,) as decimal into text using (.), and then column J14 with concatenate of I14 and H14, which leads to 10.01..15, and then use only field J14 in the NL filter. Turns out great!

  Again, thank you for all the insight you have given so far!

  Best Regards, Abu