Working on a custom form and needing help configuring a calculated field

[TravisEn]

Long time listener, first time caller.

I'm trying to help a department in my company with a custom form that is supplemental to their main request form. This secondary form uses a series of calculated fields to break down the quantities of marketing materials needing to be ordered for our 100+ retail locations, both individually and in groups, for each new request that comes in. That part I was able to figure out, but now they also have an additional criteria which is to break down the order by versions. For example, if a poster is needed for every store but five of those stores need an additional disclaimer added to them, it would be taken in as one request with two versions.

The way I have the custom form laid out now each store has two single line text fields, one where the requestor will enter the quantity the store will receive and the other to enter the version number they will receive. What I'm needing is a calculated field or fields that isolates out all the stores receiving each specific version (ex. Stores 1, 2, & 3 all need version 1) and then produces the sum of the quantity all those stores will receive.

Any help would be appreciated!

[HeatherLa2]

Hi, Travis!

Do you want this to be on calculated field within the form, or do you want to be able to pull a report that groups the data into Versions and Quantities?

Love digging in to these kinds of questions, so let us know and we can help!

[TravisEn]

I was thinking I would need it as a field on the form, but I'm open to whichever approach might function better.

I'm attaching some screenshots to help with your recommendation. Basically the form starts off with a few questions to narrow the amount of fields the requestor would have to fill out to bare minimum, then provides checkbox fields of the store locations if they need to be more specific, and then each of those checkbox choices triggers the corresponding quantity and version fields for that store. Then at the bottom is a section for the intake team that has my working quantity breakdowns and will house the quantity by version fields I'm trying to build. Also there is an admin-only space that hides some of the extra calculation fields that feed the breakdowns in the intake section.

One concern I've had with reporting for this form has been, since the quantity and version fields are all named differently, I'm not sure how unwieldy the data is going to become.

Thanks!

[Heather_Kulbacki]

This sounds to me like you'd need an IF statement to look at the version requested, but for the calculation to not stop when it reaches the first true statement of that IF.

So you need to look at all these version fields and if the entry is version 1, then add up the quantity entered in a corresponding field

Then in another field, have it look at all the version fields again if if the entry is version 2, then add up the quantity entered in a corresponding field

But I'm unsure how to get it to continue looking at the remaining version fields once it finds the first one for each version

[TravisEn]

I was figuring it would take a separate field for each version. This form been a little crash course in calculated expressions for me since we haven't used them much in our instance up to this point.

I've gotten a SUM(IF(CONTAINS chain to work in another field to grab all the stores selected but that basically was pulling from the choices in one checkbox field. I've struggled with how to make it work for this; I had tried starting with SUM(IF(CONTAINS("01",{Store 1 Version}),.. but every way I tried to put the expression to grab the Store 1 quantity kept coming back as an invalid expression. Kinda hoped if I could crack that it would let me chain more IF(CONTAINS expressions worded the same way then the SUM would add them all up, but again this has been a crash course so I'm not sure if it's possible.

Thanks!

[Heather_Kulbacki · Accepted solution]

Based on your screen shots, I'd give this a try:

SUM(IF(CONTAINS("01",{001 Springvield Version}),{01 Springfield Quantity}))

If I've got all the syntax correct and that gives you the quantity for store 1 (fingers crossed!), then I'd build on that to add another store:

SUM(IF(CONTAINS("01",{001 Springvield Version}),{01 Springfield Quantity}),IF(CONTAINS("01",{003 Gurnee-Chicago Version}),{01 Gurnee-Chicago Quantity}))

I like to build these in Notepad, then copy them over to Workfront. I tried building them in Word, but Word uses the curly quotes and when you copy that over to Workfront, the curly quote marks copy over but don't work - quote marks have to be the straight quotes.

I also like to build each piece out on a separate line so I can see where the parens and commas should be, so I'd do this one like this, then take out the returns when I think I've got it right:

SUM(

IF(CONTAINS("01",{001 Springvield Version}),{01 Springfield Quantity}),

IF(CONTAINS("01",{003 Gurnee-Chicago Version}),{01 Gurnee-Chicago Quantity})

)

Once you get 2-3 stores adding up, and you feel confident then you can go ahead with adding the rest of the stores - and you've got soooo many!

[TravisEn]

Looks like that's working! Thanks.