Question

Collection Total is Zeroing out with new item

Forms Version 10

Updated May 26, 2020

asked on May 26, 2020 • Show version history

I have a Collection table that we use for printing requests. I have the math all working based off if statements (see below). Everything is great until we add a new collection, then it zero's it out. I am thinking its due to my nested if statements, but not 100% sure

Formula: Paper type returns value 1,2,3 based on what option is selected.

=IF(PI.Paper_Type=1,PRODUCT(SUM(PI.bright,PI.ink),PI.Quant),
IF(PI.Paper_Type=2,PRODUCT(SUM(PI.coated,PI.ink),PI.Quant),
IF(PI.Paper_Type=3,PRODUCT(SUM(PI.gloss,PI.ink),PI.Quant),0)))

Pics of collection for reference and issue.

Issue when i add new job:

0 0

Answer

SELECTED ANSWER

replied on May 26, 2020

Treat the INDEX as the new variable. On the first line it should be

=IF(INDEX(Poster_Information.Paper_Type,ROW())=1,

2 0

Replies

replied on May 26, 2020 • Show version history

Hi Dustyn,

For every reference to each variable in the calculation, you will need to use INDEX(variable,ROW()) so that it only captures that set's values. Otherwise it's trying to multiply multiple sums by multiple quantities.

INDEX(Poster_Information.Paper_Type,ROW())
INDEX(Poster_Information.bright_white_paper_size,ROW())
etc.

1 0

replied on May 26, 2020 • Show version history

So just to make sure i understand correctly something like this?

=IF(INDEX(PI.Paper_Type=1,ROW()),PRODUCT(SUM(INDEX(PI.bright,ROW())),INDEX(PI.ink,ROW()),INDEX(PI.Quant,ROW())),
IF(INDEX(PI.Paper_Type=2,ROW()),PRODUCT(SUM(INDEX(PI.coated,ROW())),INDEX(PI.ink,ROW()),INDEX(PI.Quant,ROW())),
IF(INDEX(PI.Paper_Type=3,ROW()),PRODUCT(SUM(INDEX(PI.gloss,ROW()),INDEX(PI.ink,ROW())),INDEX(PI.Quant,ROW())),0)))

0 0

SELECTED ANSWER

replied on May 26, 2020

Treat the INDEX as the new variable. On the first line it should be

=IF(INDEX(Poster_Information.Paper_Type,ROW())=1,

2 0

replied on May 26, 2020

perfect! your treat it as the new variable was what got me understanding! Thank you!

For those who find this here is the code that works

=IF(INDEX(PI.Paper_Type,ROW())=1,PRODUCT(SUM(INDEX(PI.bright,ROW()),INDEX(PI.ink,ROW())),INDEX(PI.Quant,ROW())),
IF(INDEX(PI.Paper_Type,ROW())=2,PRODUCT(SUM(INDEX(PI.coated,ROW()),INDEX(PI.ink,ROW())),INDEX(PI.Quant,ROW())),
IF(INDEX(PI.Paper_Type,ROW())=3,PRODUCT(SUM(INDEX(PI.gloss,ROW()),INDEX(PI.ink,ROW())),INDEX(PI.Quant,ROW())),0)))

2 0

You are not allowed to follow up in this post.

Question

Question

Collection Total is Zeroing out with new item

Answer

Replies

Sign in to reply to this post.