Odd behaviour of if-then-else

The app made is based on 2 google tables-Pension Calculator and PaymentPeriod. I created a column in data editor Category which selects one of the 3 ‘Choices’ -eg. RBI_FP. Another column named ‘DR’ is created that selects the applicable rate of DR for a particular Choice.DR is a if-then column. The screen shots of the app and if then column are given below.
What is odd that’ then’ entry is not being picked up by the app from the first case. However the same is picked up when given in ‘Else’ case,Moreover,if the first case is kept empty even then it picks up the Else.
Is it a bug or something else done wrong by me?
please help.

PensionC - Google Sheets - 1 (2)

PensionC - Google Sheets -table2

your RBI_FBs is in the choices column, not category… that’s why logic can’t see it

It is shown in category column only when the user chooses .the app behaves in order with all the 3 choices, if any of the 3 choices is shown in "Else’

I don’t understand what you mean… I see that in your if-else column… first condition is looking for value in the category column, but I don’t see that value there… i see it in choices column

It shows in category column only when the user chooses the category from the app as category column is created in data editor only and is dependent on choices.

ok, what I understood from your explanation, is that the first condition is not working… and it goes to the else option…
that’s why I said that there is no value like that in the category column, from what I can see in your screenshots… that’s why is not working

maybe try to reframe the question

Yeah, I’m not understanding your problem. All of the screenshots in your first post make it appear that it’s working correctly, although the last screenshot in your first post makes it appear that you changed your choice from ‘RBI/other bank on/after 1.11.17’ to ‘RBI_FPs’. However, that last screenshot in your first post still seems to be giving the correct results based on that choice.

Where I get confused is the screenshot in your second post. There, it seems to be showing the wrong DR value and seem to be the problem, but I’m not understanding your description of the problem.

Could you share the url for your app, so I can at least understand what’s happening? If so, would it be possible for you to allow the app to be copied so we can see what’s happening underneath?

I do have a couple of suggestions. I notice that your Basic Pension and Category columns are not user specific. If you intend for multiple users to use this app at the same time, I would suggest that you use user specific columns instead, so all user don’t see and overwrite each other’s pension amounts.

Also I have a suspicion that part of your problem is that you are trying to return a value of blank and that it’s possibly used in a math column. This may be causing the javascript to fail. I think your DR value for RBI_FPs should really be 1 instead of blank.

As desired the copy-able link is https://worried-company-3561.glideapp.io/.
About your observations I have to say

  1. the app is working subject to the condition that if-then DR coulmn is set as given in screen shot
    Otherwise i.e. if your put condition in first ‘IF’ { shown empty in screen shot )and make ‘ELSE’ empty because it becomes redundant in the situation, the DR value 2.025 is not picked. Not only this Any of the choices if populated in the First ‘Else’ are not picked as shown in screen shot below.
    In simple words the First if is not working for any of the choices and subsequent 'IF" are working which show X mark against them. It seems odd to me.
    Further I am sure that if I start again afresh and delete this app , I may get rid of the problem. But I wish to learn .
    Please also help by advising other alternatives to see the same results of the app without 'if.'I have noted your suggestions. However, this app is to be used by retirees, who are content to see the results on screen only as in case of “Present Value Calculator”

The problem is that in your Payment Period → Choices column, there is a trailing space after your first option. So it never matches. Remove that space, and it works as expected.

Screen Shot 2022-03-12 at 4.23.41 PM

This is always a danger when you “hard code” complex strings in such a way. An easy way to avoid this would be to use two columns for your choice options - one for display, and one for the value that gets written. And then use something simple for the values, such as 1, 2, 3.

One other comment I’ll make is that your choice component is writing to a basic column. If you will have multiple users of the app this will be a problem, as each users choice will over write the value for all other users. You should make that column User Specific.

Edit: On re-reading earlier comments I see that @Jeff_Hager already pointed out that your Category (and Basic Pension) columns should be User Specific. And yes, both should be.

1 Like

Thanks so much for identifying the problem. I am sorry I could not identify the same.
Keeping in view your suggestions, I have included the same to the extent I understood these.A copy of the app with copy -able link is attached for perusal and confirmation about the proper inclusion of suggestions.
while changing the existing Basic columns in the existing app to user specific, I had to insert the new column as user specific and delete the old Basic column from the google sheet. Is it the proper way or not ?
Further to avoid hard coding , I inserted another column category with if -then with nos 1,2,3 and although it works, I shall be thankful to have your valuable comments in the matter.

Thanks again.

Yes, that’s correct. Basic columns that are connected to Google Sheets can only be deleted from the Google Sheet.

What you have looks fine. I guess the only other suggestion I might make is that you could actually eliminate that if-then-else column (DR) altogether if you wanted to. All you need to do is include the actual values in your choice list, ie. like this:

Screen Shot 2022-03-13 at 10.53.25 AM

And then you could use the selected value directly in the math column, without having to bother with the if-then-else. I guess the only reason then to retain the if-then-else would be if you wanted to set a default value (to cover the case where the choice selection is empty).

1 Like

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.