Question

forms formulas

Forms Version 10

Updated April 19, 2021

asked on April 14, 2021

If this works: ="TWP "&INDEX(Location_Area.TWP1,ROW())&" and Rge "&INDEX(Location_Area.Rge1,ROW())

And then I just add an IF statement to say if the Type field is 4, then ...

Then why doesn't this also work: =IF(LEN(INDEX(Location_Area.Type,ROW()))=4),"TWP "&INDEX(Location_Area.TWP1,ROW())&" and Rge "&INDEX(Location_Area.Rge1,ROW()))

In this example, my type field has these items and I changed the Value for #4 to "4" attempting to simplify the formula:

0 0

Answer

SELECTED ANSWER

replied on April 16, 2021

=IF(OR(LEN(INDEX(Location_Area.TWP1,ROW()))=0,LEN(INDEX(Location_Area.Rge1,ROW()))
=0,LEN(INDEX(Location_Area.TestHideAlways,ROW()))
=1),"",CONCATENATE("TWP ",INDEX(Location_Area.TWP1,ROW())," and Rge ",
CONCATENATE(INDEX(Location_Area.Rge1,ROW()))))

That doesn't work? With the bold TWP added in at the front?

1 0

replied on April 16, 2021

I may have been adding it in the wrong place... thanks!! I have internet connection problems again right now (working remotely at home) so I might not be able to test this until Monday.

0 0

replied on April 16, 2021 • Show version history

That works! Awesome, thanks Jared.

I don't know why I/we couldn't get the very first type of option working (the one I first reported on to start this post). There are four different types of locations we need to collect as people don't seem to want to give them in a format that we can use over time to compare notes successfully so we had to go with the table of fillable parts and then collect them separately into one location field.

I could get the two out of three of those working easily. Last week I got the third one working and then this week I just could not make that fourth one work. In the end, I copied the second one which has three fields instead of two to collect and created a hidden third field to test with (I already knew I couldn't make it work with two) and then just changing the field variable names. So, with a third field hidden but allowed for use in the formula, we got it working!!

Now to transfer this formula into the live form that is already being used!

1 0

Replies

replied on April 14, 2021 • Show version history

Too many close parentheses. Your IF fully closes here (bolding every other to match them up)

IF(LEN(INDEX(Location_Area.Type,ROW()))=4)

If that's just the condition on the if, you just want

IF(LEN(INDEX(Location_Area.Type,ROW()))=4, True, false)

0 0

View 1 previous reply

replied on April 15, 2021

Thanks, that was very helpful, Jared! I like explanations! I'm still getting invalid showing up, so there must be something else wrong as well? This is the latest I've tried:

=IF(LEN(INDEX(Location_Area.Type,ROW()))=4), True, "TWP "& INDEX(Location_Area.TWP1,ROW())&" and Rge "& INDEX(Location_Area.Rge1,ROW())

0 0

replied on April 15, 2021

IF(LEN(INDEX(Location_Area.Type,ROW()))=4), True, "TWP "& INDEX(Location_Area.TWP1,ROW())&" and Rge "& INDEX(Location_Area.Rge1,ROW())

Same problem

I think you have a wrong close paren after the 4 and instead need to add one more at the end

IF(LEN(INDEX(Location_Area.Type,ROW()))=4, True, "TWP "& INDEX(Location_Area.TWP1,ROW())&" and Rge "& INDEX(Location_Area.Rge1,ROW()))

Sometimes it's easier to match them up with colors. Every time you open, use a different color, then match that color with the corresponding close.

1 0

replied on April 16, 2021

Thanks, Jared! It is now no longer "invalid", but the formula is filling the field with the items in the second half of the formula even when the "Type" field is NOT 4. ???

Oh my! I might know why:

The value for option four in the dropdown is 4.
I was thinking the IF statement would only see the 4 when option four was picked in the drop down. Is that NOT the case?

0 0

replied on April 16, 2021 • Show version history

Your condition LEN(INDEX(Location_Area.Type,ROW()))=4 says to me

Take the value in Location Area column of this row of the table, and give me the length of it. So if your table had that calculation for Col 2, it would be

|Location Area| Col 2|

| apple | 5 | =4? False

| pear | 4 | =4? True

| 4 | 1 | =4? False

You're getting the length of the words/numbers in the column. If you want the type to just =4, remove the LEN( ) making sure to remove the correct parentheses.

IF(INDEX(Location_Area.Type,ROW())=4, True, "TWP "& INDEX(Location_Area.TWP1,ROW())&" and Rge "& INDEX(Location_Area.Rge1,ROW()))

0 0

replied on April 16, 2021

Thanks, Jared. That does help! In testing, I still have one problem: The formula is supposed to fill the target field with "TWP..." only if it finds the 4th option in the Type drop down. In testing, I'm still getting these results:

0 0

replied on April 16, 2021

0 0

View 6 previous replies

replied on April 16, 2021

As a test, add another two columns in your table and set them to the calcs

=INDEX(Location_Area.TWP1,ROW())

=INDEX(Location_Area.Rge1,ROW())

Do those return the values you are looking for? Which columns are TWP1 and Rge1?

In your initial post, you said

If this works: ="TWP "&INDEX(Location_Area.TWP1,ROW())&" and Rge "&INDEX(Location_Area.Rge1,ROW())

So I assume that part was working correctly already.

QUESTION: Is that location field where the calculation is inside the table and wrapped around in the screenshot? Or is that field outside of the table? If it's outside of the table, the row() formula won't work. Row() means "the row I'm on", so if the calculation is used in a field outside of the table, it's not in any rows.

0 0

replied on April 16, 2021

Thanks, I'll give that a try, and sorry, I'm having extreme internet problems today so posting back to here is ... spotty ... if I can get through.

0 0

replied on April 16, 2021

Re, your question: The location field is a multiline field outside the table that is filled by Javascript after the form is submitted. It pulls only the four collection fields (the last one that collects all the pieces of each row of the table. The idea is, this way, I can create a report that doesn't have to have four location columns. I can have just the one location column and I can sort the report to get all the like kinds together if looking for patterns.

0 0

replied on April 16, 2021

You also asked, which columns are TWP1 and Rge1. They are the two blanks. And yes, the formula works if I'm not trying to keep it clear when the TWP/Rge option isn't being used.

0 0

replied on April 16, 2021 • Show version history

Is this what you meant for me to do in a test:

They filled properly using your formulas (I didn't type in the two new columns)

0 0

replied on April 16, 2021 • Show version history

And currently, this is in my hidden (well, not currently hidden) collection field (labelled above as TWP/Rge):

=IF(INDEX(Location_Area.Type,ROW())=4, TRUE, "TWP "& INDEX(Location_Area.TWP1,ROW())&" and Rge "& INDEX(Location_Area.Rge1,ROW()))

0 0

replied on April 16, 2021

For your field rules, do you have it set to "Ignore data when the field is hidden" or "Save data..."?

If you ignore the data, the hidden fields will be blank. You need to save the data while the field is hidden.

If that's not it... I'm pretty confused now. The first collection you showed has all sorts of different fields than the latest collection you've shown. Is that all the same collection with different hidden fields? Or another collection?

The formula I gave you should be able to fill the TWP/Rge fill correctly based on which was selected in Type of Address and the values in TWP and Rge. I don't know why any JS isn't working.

0 0

replied on April 16, 2021

Set to Save data:

0 0

replied on April 16, 2021 • Show version history

Jared, to clarify:

1) The formula IS actually filling correctly now. Thanks! It's just filling regardless of the IF statement.

2) The JavaScript that is working on the multiline field that is not part of the table IS working correctly, as well.

3) The working JavaScript is what is showing me that the formula for TWP/Rge is actually filling regardless of the IF statement. (At least, that is what it appears to be telling me.)

0 0

replied on April 16, 2021

The form is to fill the collection fields when the right type of address is used, otherwise leave them blank so that the Location field that is going to be used as a column in a report will always have the right address:

0 0

replied on April 16, 2021

Strangely enough, this works but I can't get it to populate the TWP at the beginning of the resulting statement:

=IF(OR(LEN(INDEX(Location_Area.TWP1,ROW()))=0,LEN(INDEX(Location_Area.Rge1,ROW()))
=0,LEN(INDEX(Location_Area.TestHideAlways,ROW()))
=1),"",CONCATENATE(INDEX(Location_Area.TWP1,ROW())," and Rge ",
CONCATENATE(INDEX(Location_Area.Rge1,ROW()))))

This is what shows up when you first choose the rural address option and properly leaves the collection field blank until at least one of the pieces is filled:

But, I can't get it to add the TWP before the resulting collection:

When I try to add that piece into the formula, it then just fills with TWP and nothing else.

0 0

You are not allowed to follow up in this post.

Question

Question

forms formulas

Answer

Replies

Sign in to reply to this post.