General

Re: Formula for Separator in Text Box with Concatenated Fields

rmsphoto — Tue, 07 Dec 2010 21:09:20 GMT

BINGO! Thanks for posting. It finally work for me now. - I could not figure out why this has not been working. (Not that I know why now, just that it works now...)

Re: Formula for Separator in Text Box with Concatenated Fields

Jimmy — Tue, 05 Oct 2010 02:28:56 GMT

Just have it do the same thing as the rules on fields 1-4 are doing.

Re: Formula for Separator in Text Box with Concatenated Fields

ncarson — Tue, 05 Oct 2010 02:18:41 GMT

No, I don't have a rule like that. Can you tell me what you had in mind for a rule like that? If it works maybe I can apply it in some other spots that are behaving the same way.

Re: Formula for Separator in Text Box with Concatenated Fields

Jimmy — Tue, 05 Oct 2010 01:04:18 GMT

I see. Does field6 have a rule to recalculate the formula when its value is changed?

Re: Formula for Separator in Text Box with Concatenated Fields

ncarson — Mon, 04 Oct 2010 17:08:23 GMT

I have only one Rule on field5: Condition: field5 is blank Action: Set field6 = ""

Field6 has conditional formatting to hide if field5 is cleared.

Re: Formula for Separator in Text Box with Concatenated Fields

Jimmy — Mon, 04 Oct 2010 16:23:57 GMT

ncarson:
The odd thing is if I deselect field5 and then select any other check box, then the field6 text in the formula text box goes away. But if I don't check another check box, it remains.

This probably means that you need to rearrange the order of your rules on field5.

Re: Formula for Separator in Text Box with Concatenated Fields

ncarson — Mon, 04 Oct 2010 16:13:20 GMT

I tried this edit to the formula, but field6 text still stuck in the formula text box. Then I tried adding a Rule to field5 to reset field6, but the field6 text still stuck. The odd thing is if I deselect field5 and then select any other check box, then the field6 text in the formula text box goes away. But if I don't check another check box, it remains. I also tried it with both the formula edited like above and with field5 having the Rule about resetting field6 but it still behaved the same.

Re: Formula for Separator in Text Box with Concatenated Fields

Jimmy — Mon, 04 Oct 2010 15:37:42 GMT

How's this:

substring(eval(eval((field1 | field2 | field3 | field4 | field6[field5 != ""])[. !=""], 'concat(", ",.)'), ".."), 3, 1000)

The other option is to put a rule on field5 to reset field6's value when field5 is unchecked, but depending on your design, you may or may not want to retain the value in field6 in case the user checks field5 again.

Re: Formula for Separator in Text Box with Concatenated Fields

ncarson — Mon, 04 Oct 2010 14:52:03 GMT

I did finally get that other section to work by removing the formula and starting over -- there must have been one little thing off in the way I had added it the first time. But thankfully it is working perfectly now! Unfortunatley, there is one more issue I'm having. One section that has multiple check boxes has a final check box that is "Other" and when checked will open a section holding a text box to the right for a user to fill in anything that is not able to be categorized in the other check boxes provided. When I apply the formula to this section, all check boxes work as they should, and when deselected the value disappears from the text box where the formula is applied. If I fill out the "Other" text box with anything and it gets added to the text box with the formula, the text gets added just fine, but if I deselect the check box that controls the "Other" text box, the text that was entered does not disappear from the text box with the formula. I will recreate a facsimile here:

Check box1 (Field1=Apple)
Check box2 (Field2=Orange)
Check box3 (Field3=Banana)
Check box4 (Field4=Grape)
Check box5 (Field5=Other) Other Text box (conditional formatting set to hide unless Field5 has been checked) (Field6=blank)

Text box with formula: substring(eval(eval((field1 | field2 | field3 | field4 | field6)[. !=""], 'concat(", ",.)'), ".."), 3, 1000)

Results when all boxes checked on Text box with formula: Apple, Orange, Banana, Grape, Text typed in Field6

Deselect Field4 (user made a mistake): Apple, Orange, Banana, Text typed in Field6

Deselect Field5 that controls Field6: Apple, Orange, Banana, Text typed in Field6

Once data is typed in Field6, it stays in the Text box with the formula.

If it would be helpful, I could send you a copy of my form as you mentioned in your last email, just let me know. But I thought I'd first try to relay it this way so you wouldn't have to wade through my form. I promise if you can help me with this I really believe this will be the final problem with separators I'm having on this form. And again, thank you.

Nalani

Re: Formula for Separator in Text Box with Concatenated Fields

Jimmy — Mon, 04 Oct 2010 05:07:31 GMT

Glad to help, and I hope you figure out this last problem. If you still can't get it after a while of troubleshooting, you can e-mail the form to me at james dot rishe at qdabra dot com, and I can have a look.

Re: Formula for Separator in Text Box with Concatenated Fields

ncarson — Mon, 04 Oct 2010 03:32:19 GMT

Jimmy -- I just applied the formula to another section that is identical to the one I'm having the trouble with - there are 30 fields in it just like the other one; this one worked like a charm, confirming that the problem with that other section is unrelated to the formula. So, again, I want to say thank you very much. You have been a huge help with a monster of a form I've had to put together in which adding a separator in several locations was in big demand. Thank you for all of your time and attention to my problem. I feel sure now I can get the form wrapped up, even if I have to redo that one section.

Nalani

Re: Formula for Separator in Text Box with Concatenated Fields

Jimmy — Mon, 04 Oct 2010 02:42:23 GMT

No problem that I know of, but I've never tried the formula with more than a few fields.

Re: Formula for Separator in Text Box with Concatenated Fields

ncarson — Mon, 04 Oct 2010 02:28:40 GMT

I just tried another section and the formula worked just fine. There must be something else unusual in the section where I'm having trouble. I will continue looking into that. There wouldn't be a problem with a large number of fields in the formula (like 30 or 50) as far as you know would there?

Re: Formula for Separator in Text Box with Concatenated Fields

ncarson — Mon, 04 Oct 2010 02:16:44 GMT

The unchecked values do not have a space in them. Here's the formula:

substring(eval(eval((AirTopicsHide1 | AuditDiscloseHide1 | BBEnvSeminarsHide1 | BankruptcyHide1)[. != ""], 'concat(", ", .)'), ".."), 3, 10000)

Re: Formula for Separator in Text Box with Concatenated Fields

Jimmy — Mon, 04 Oct 2010 02:03:23 GMT

Is there any chance the checkboxes in that section have blank spaces in the unchecked value? Could you post your formula?