Question

SUB Function not working

Forms Version 10

Updated April 11, 2022

asked on April 8, 2022

Hi all, I found lots of SUB threads but I am still not getting it to work. I'm trying to calculate the Outstanding Qty of hardware inventory.

--Table is hardware_tbl3

--This is in Outstanding Qty: =SUB(INDEX(hardware_tbl3.Original_Qty,ROW()),INDEX(hardware_tbl3.Total_Received_Qty,ROW()))

I also tried: - =(INDEX(hardware_tbl3.Original_Qty,ROW())-(INDEX(hardware_tbl3.Total_Received_Qty,ROW())))

--The TR2 will be populated with the Total Received value. The formula in

--Total Received Qty (Total_Received_Qty) is: =SUM(INDEX(hardware_tbl3.TR2,ROW()),INDEX(hardware_tbl3.Received_Qty,ROW()))

What am I missing here? All my other calculations work... Knocking head now...

Thanks for any lesson :)

Sue

0 0

Answer

SELECTED ANSWER

replied on April 8, 2022 • Show version history

Formula's look fine based on the names you provided.
- I'd make sure they are all Number fields (some previous versions of forms used to object to singleline fields in numeric calculations)

- Check your variable names (Use the variable picker)
- Are those the same fields in the 2 calculations as they are not the same?

--This is in Outstanding Qty: =SUB(INDEX(hardware_tbl3.Original_Qty,ROW()),INDEX(hardware_tbl3.Total_Received_Qty,ROW()))

I also tried: - =(INDEX(hardware_tbl3.Original_Qty,ROW())-(INDEX(hardware_tbl3.Total_Received_Qty,ROW())))

--The TR2 will be populated with the Total Received value. The formula in

--Total Received Qty (Total_Received_Qty) is: =SUM(INDEX(hardware_tbl3.TR2,ROW()),INDEX(hardware_tbl3.Received_Qty,ROW()))

1 0

Replies

replied on April 8, 2022 • Show version history

I'm a little stumped because your formula worked for me in testing:

=SUB(INDEX(hardware_tbl3.Original_Qty,ROW()),INDEX(hardware_tbl3.Total_Received_Qty,ROW()))

I guess I'm not really visualizing how your table looks and interacts - can you post some screenshots?

1 0

replied on April 8, 2022

I think @████████ might be right about singleline fields and double-checking all of the variable names from the picker. In trying to replicate the way you described your table, I kept getting errors in the calculation, and was struggling to see why. Finally I realized that Received_Qty had originally been a singleline field, and when I changed it to a number field, it made a new variable named Received_Qty_1 and I hadn't updated the formulas to account for that different variable name. After I fixed that, the calculations appear to be working correctly for me:

So it seems like the issue may not be the formulas you are using, but one of the issues Steve mentioned about not being a number field or having the incorrect variable names.

0 0

View 1 previous reply

replied on April 11, 2022

Everything look good. All numbers and I copied and pasted variables names. I even added that table to another page to make sure there weren't rouge fields. It was clean and looked exactly as I have on main page. I heard from my vendor so we are jumping on a call at 11. Will post our finding for anyone interested after call. Thanks again for all your input!!

0 0

replied on April 11, 2022

So I created a new outstanding, thinking that maybe the original field type was NOT number. I was going to do it for all of the fields, however the outstanding field was the culprit. It must have originally been another type and when I changed it the real name changed but I don't see the actual field name once I changed the field type. I will be more careful and define my table before building it.

Unless someone has a better best practices or rule of thumb to go by :)

Thanks again everyone and happy Spring!!

1 0

replied on April 11, 2022

So glad to hear you got it working!

1 0

You are not allowed to follow up in this post.

Question

Question

SUB Function not working

Answer

Replies

Sign in to reply to this post.