Combining columns

[leahcarn]

I’m trying to combine the information in two spreadsheet columns into a third column, which will be a reference to another table. Only one of the first two columns will have information in it at a time.
The expression I’m currently using is IF(ISNOTBLANK([Inactive Member]), [Inactive Member], [Active Member]), and it works fine in testing but returns blanks in the app itself, where the information is coming from the [Inactive Member] column. Where the information is coming from the [Active member] column it works fine.
Help?

[Bellave_Jayaram]

You could try this and handle the case of both being blank as an ERROR:

IF(ISBLANK([Inactive Member]), IF(ISBLANK([Active Member]), "ERROR", [Active Member]))

[leahcarn]

I don’t think that would solve the problem I’m having, where the expression is not correctly populating the third field when the original information is stored in the [Inactive member] column.
There will never be a situation where both the first two columns are blank.
I hope this clarifies things

[Steve]

Please provide screenshots showing:

1. The column configuration for the Active Member column.

2. The column configuration for the Inactive Member column.

3. The expression as entered in the app editor.

4. A successful test.

5. A blank return “where the information is coming from the [Inactive Member] column”.

[leahcarn]

Sorry for my late reply, I’ve been away from my computer over the weekend. I’ll do the screenshots in separate posts, as there’s quite a few.
Active member column

[leahcarn]

Inactive member

[leahcarn]

Borrower column, showing the expression as entered into the app editor

[leahcarn]

Passed test (Rachael Nicoll is the inactive user)

Failed execution, returning blank for borrower column

[Steve]

Wow! Thanks for the outstandingly-complete collection of screenshots!!! Unfortunately, everything you posted looks fine to me, so we have to consider additional angles.

My first though is that the Active Y/N column in the OCL Membership Database contains blank values in some rows (e.g., for Meh dai). Because of the peculiar behavior of the is-equal-to (=) operator in AppSheet, ([Active Y/N] = "Y") would be TRUE if the Active Y/N column value is blank. Both the is-equal-to (=) and is-not-equal-to (<>) operators will return TRUE if the left operand is blank, regardless of the right operand. The easiest fix is to put the operand that will always be non-blank on the left side: ("Y" = [Active Y/N]).

[leahcarn]

Ok, so what I think you are saying is that rotating the expression will make it return the correct false result where Active Y/N is blank. Therefore this would carry through to the Borrower column preventing blank results there?

The expression for Active Y/N is

So it shouldn’t be returning blanks, and doesn’t appear to be, in testing. Every column is either Y or N

Nevertheless I tried rotating the expression, and I didn’t get any results at all (you can see the expression in the top right)

Previously in testing it was returning the list of active members

Is it worth rolling it out and seeing if it works differently in practice?

[Steve]

leahcarn:

Ok, so what I think you are saying is that rotating the expression will make it return the correct false result where Active Y/N is blank.

Correct.

leahcarn:

Therefore this would carry through to the Borrower column preventing blank results there?

Theoretically, yes. If blank Active Y/N column values are the problem.

leahcarn:

So it shouldn’t be returning blanks, and doesn’t appear to be, in testing. Every column is either Y or N

I agree, so my suggestion won’t fix anything.

leahcarn:

Nevertheless I tried rotating the expression, and I didn’t get any results at all

That’s very odd!

leahcarn:

Previously in testing it was returning the list of active members

*Inactive Member is an active member?

leahcarn:

Is it worth rolling it out and seeing if it works differently in practice?

Nope. We need to look elsewhere.

[Bahbus]

What are each of these columns? Specifically the first 3 after the date.

EDIT: Nevermind, I figured it out from a different screenshot.

[Bahbus]

Just for silliness, what (if anything) happens if you were to change the expression for Borrower to:

IF(ISBLANK([Inactive Member]), [Active Member], [Inactive Member])

[leahcarn]

That works!! I have no idea why but it works!! Thank you so much

[Bahbus]

…
…
…
I am literally stunned. I did not expect it to work. Maybe a bug in the backend?

[Steve]

Yeah, I’d call that a bug.