Pull multiple fields into one field based on the response

replied on January 10, 2023 • Show version history

I ended up creating two 'extra' fields that would be hidden on the form. The first is just a count of how many "Yes" values are selected. The second is to hold the string. To start with for each "Yes", I concatenate the 'Application name" and ", ". Then If there more than two selected the " and" replaces the second to last comma. In both cases a period replaces the last comma that is created in the concatenate functions.

The formula for the "Count Yes" field :

=COUNTIF([Application_A, Application_B, Application_C, Application_D], "=Yes")

The formula for the Email Formatting field:

=IF(Count_Yes>1, 
SUBSTITUTE(
SUBSTITUTE(
CONCATENATE(
IF(Application_A="Yes", "Application A, ", ""),
IF(Application_B="Yes", "Application B, ", ""),
IF(Application_C="Yes", "Application C, ", ""),
IF(Application_D="Yes", "Application D, ", "")
),
",",". ", Count_Yes
),
","," and", Count_Yes-1
),
SUBSTITUTE(
CONCATENATE(
IF(Application_A="Yes", "Application A, ", ""),
IF(Application_B="Yes", "Application B, ", ""),
IF(Application_C="Yes", "Application C, ", ""),
IF(Application_D="Yes", "Application D, ", "")
),
",",". ", Count_Yes
)
)

I hope this is what you were looking for :)

EDIT: One change I would most likely do on my own forms, would be to have a field that holds just the basic concatenated string output. This way if new Applications are added or some removed from the form I would only need to change the one concatenate function. It would also make the Email Formatting field's function easier to read (in my opinion).

3 0

View 7 previous replies

replied on January 10, 2023

I feel like this should work. The form itself is happy with the code, but when I run it on the form and select the first applications, I get a calculation error on each field reference.

=IF(TotalApprovedQualifiers>1,
SUBSTITUTE(
SUBSTITUTE(
CONCATENATE(
IF(ecHomelessVerifyYN="Y", "McKinney-Vento, ", ""),
IF(ecFosterChildVerifyYN="Y", "Texas Foster Care, ", ""),
IF(VerifyEmergentBilingualStatus="Y", "Identified as emergent bilingual, ", ""),
IF(ecFRVerifyYN="Y", "Free and Reduced, ", "")
),
",",". ", TotalApprovedQualifiers
),
","," and", TotalApprovedQualifiers-1
),
SUBSTITUTE(
CONCATENATE(
IF(ecHomelessVerifyYN="Y", "McKinney-Vento, ", ""),
IF(ecFosterChildVerifyYN="Y", "Texas Foster Care, ", ""),
IF(VerifyEmergentBilingualStatus="Y", "Identified as emergent bilingual, ", ""),
IF(ecFRVerifyYN="Y", "Free and Reduced, ", "")
),
",",". ", TotalApprovedQualifiers
)
)

0 0

replied on January 10, 2023 • Show version history

I think the issue may be when none are selected (EDIT or only "No" is selected), so to fix this another IF statement was needed. But first I did as I mentioned in my Edit above and have a separate field that just does the base cunctation.

The formula for the "Count Yes" field is the same as above.

The formula for the Approved Applications List:

=CONCATENATE(
IF(Application_A="Yes", "Application A, ", ""),
IF(Application_B="Yes", "Application B, ", ""),
IF(Application_C="Yes", "Application C, ", ""),
IF(Application_D="Yes", "Application D, ", "")
)

The formula for the Email Formatting field:

=
IF(Count_Yes>1, 
SUBSTITUTE(
SUBSTITUTE(
Approved_Applications_List,
",",". ", Count_Yes
),
","," and", Count_Yes-1
),
IF(Count_Yes=0, "none approved", 
SUBSTITUTE(
Approved_Applications_List,
",",". ", Count_Yes
)
)
)

0 0

replied on January 10, 2023

Adding in the other field does make sense and I think that helps a lot because qualifiers will change. The second field works as you explained but still have the same issue with the final Email Formatting. I think you are right, there are fields that may be NULL or No, but adding that piece in didnt seem to change the results. Its giving an error on the calculating the Count Field and the List field.

0 0

replied on January 11, 2023 • Show version history

I have a few questions: Is the count field a number field? Mine is, but it may not matter. Are the fields you are counting drop downs, radio buttons or something else?

Also can you share the function you have in the count field. If it has an error it would be understandable that error would be passed down the line.

0 0

replied on January 11, 2023

I'm sorry, my reply might have been confusing. The error is on the Email Formatting field, it is referencing the Count Field and List Field.

My application options are Radio buttons of yes or no. The count field is counting perfectly and the List Field is populating accurately as well.

0 0

replied on January 11, 2023 • Show version history

Can you share your current formula in your Email Formatting field then. Maybe there is a parenthesis off or a comma missing that I might be able to spot.

Also if possible a screen shot of the three fields might help me see what might be happening.

0 0

replied on January 11, 2023

=IF(TotalApprovedQualifiers>1, 
SUBSTITUTE(
SUBSTITUTE(
ecApprovedPKPrograms,
",",". ", TotalApprovedQualifiers
),
","," and", TotalApprovedQualifiers-1
),
IF(TotalApprovedQualifiers=0, "none approved", 
SUBSTITUTE(
ecApprovedPKPrograms,
",",". ", TotalApprovedQualifiers
)
)
)

I looked over it and it seems to be fine. On the form itself, the sytax is legit but maybe something is off when grabbing the application names

0 0

SELECTED ANSWER

replied on January 11, 2023

I think I know the issue and I tried it on my test form to check. Your TotalApprovedQuaulifiers fields needs to be a Number field. From the looks of your screenshot I think it's a single line field instead.

I added a single line field and put the count function in it and then used that field for the email formatting text area and I got a matching error.

0 0

replied on January 11, 2023

Worked perfectly! Thank you so much for this, you just saved me hours of setting it up through workflow

1 0

replied on January 12, 2023

I am happy you got it working and I was able to help!

0 0

replied on January 10, 2023 • Show version history

Did you include the commas as parameters in your concatenate call. IE: ","

How is having the values in a CSV format more helpful when using the process diagram? It seems when setting up your approval conditions you would want to specify A=Yes and B=Yes, etc.

Edit:

Oh maybe you mean the approver is approving 1 or more of many items.

In that case getting it into a CSV should be as simple as using concatenate A,",",B...

0 0

View 1 previous reply

replied on January 10, 2023

Yes the Requester can select A B C and D

The approver may only approve B and C

I tried including the "," in the formula but it either error'd the formula or would show extra commas because they insert of the comma is static not based on the value of the field being Y.

Ex: B=N and C=Y

The overall goal is to get the approved variables in an email so the submitter can see what they were approved for. Since there is currently not a way to add rules in the email, I was thinking I could get all the results in one line. I may have to breakdown and create the email in workflow with a table array.

0 0

replied on January 10, 2023

Why not just populate the email body with

A : {/dataset/A}

B : {/dataset/B}

This would show what they selected for A through D

1 0

replied on January 10, 2023

We did that but realistically there are up to 6 options, so there is a lot of white space if only one was approved

0 0

replied on January 10, 2023

Oh then you can use the IF methods as Genny posted to exclude anything with a NO

0 0

Question

Question

Pull multiple fields into one field based on the response

Answer

Replies

Sign in to reply to this post.