Problems with Show if

Denis2 · 04-17-2019 01:25 AM

Hi, I’m new to Appsheet.
I have built a prototype app to help clients record gas and electricity meter readings. There are three tables, Sites, Electricity Readings and Gas Readings. The two reading tables reference the Sites table and are each set to be “part of” that table. Broadly speaking, the app works but I want to refine it and am having problems with “show if” logic.

There is a column is Sites called Utility and for each row it contains "Electricity" or "Gas". There are rows in Sites for each site/utility combination and I have concatenated in Excel Site name and utility and set that as the key.

There is also column in Sites named Profile of type Text.

I have created form view for each reading table and set them as inline so if you click on a site it takes you to the inline views for electricity and for gas. However, if the site I click on is for electricity only, I want to show only the electricity inline form and vice versa. I am trying to use show if logic and have tried = and contains(). Each is accepted as correct but neither works.

For electricity if the profile is "04", I want to ask for a Day reading and a Night reading else Day only.

Can someone help, please?

Kirk_Masden

Hi @Denis! I’m not sure if I have enough knowledge to help but I’d like to try. My problem is that I’m having trouble visualizing your situation, even though you have described it in detail. I wonder if you could provide some screenshots that you think might be illustrative of your problem.

I’m imagining that you have made actions that you have set to “inline.” Is that right? If so, are you relying on “show if” in the action itself or have are you using the “show if” setting in the table?

Denis2

Kirk,

Thanks for coming back to me. I can build some screenshots for sure but, as this is a prototype with dummy data can I share it so you get the editor’s view? Which would be better?

Best wishes,

Denis

Denis2

I’ve gone ahead and shared it with you as co-author so you can see my edit.

Many thanks again,

Denis

Kirk_Masden

Thanks! This is the first time anyone has shared an app with me and I’m not quite sure how it works. I looked at my Gmail and at my AppSheet editor but didn’t see anything.

Lynn

Hi @Kirk_Masden Check your "My Apps " page and scroll down to “Shared Apps” , you should see @Denis app there.

Denis2

Lynn,

Thanks for trying.

Denis

Mike_A

@Denis - maybe easiest is to just post the actual ShowIf formula you have in each of the virtual columns (Related Records…)? This should work but need to see the exact ShowIf to allow others to spot possible issues?

Denis2

Here are some screenshots. I will also post to site.

Thanks,

Denis

Lynn

Hi @Denis What happens if you use 04 instead of “04” ?

Denis2

Hi Lynn,

Makes no difference.

Denis

Denis2

Kirk,

I attach some screenshots that I hope will help.

Kind regards,

Denis

Steve

The format for CONTAINS() is:

CONTAINS( whole-text , partial-text )

which answers the question: does whole-text contain partial-text?

The whole-text argument should be an expression that produces a single Text value. In the Show? expression for the Night Read column of the Electricity Meter Readings table, you have:

contains((sites[profile]),"04")

sites[profile] is a column list expression, producing a List of the values of the profile column from all rows of the table, which I suspect is not your intention. If you only want to look at the profile column value for the current row, instead use:

contains([profile],"04")

Based on your earlier statement:

I would guess profile would be exactly 04, rather than containing it in some longer text value. If so, I propose using = rather than CONTAINS():

([profile] = "04")

The = operator in AppSheet has some peculiar behavior that might cause a problem here, though: if the value on the left side of = is blank, the result of the = comparison will be TRUE regardless of the right side value. If the profile column might ever be blank, this expression might unexpectedly be TRUE. So it’s a good idea to ensure the left side value is not blank:

and(
  isnotblank([profile]),
  ([profile] = "04")
)

Denis2

Steve,

This doesn’t make any difference (Sorry if I’ve duplicated an email).

Denis

Steve

The use of a Show if expression on the form view will have no effect in what you’re trying to accomplish. That expression affects the display of the view in the main menu or along the bottom navigation bar only if the view’s position is one of those. Show if in views has no effect at all if the view is in the ref position.

Denis2

Many thanks,

So how fo I restrict/constrain vews

Denis

Kirk_Masden

Thanks @Lynn! I guess the share didn’t work because I only have “Deployed” and “Prototype” as visible categories.

Kirk_Masden

Thanks @Denis! The images helped me get a clearer idea of what the problem might be. I’m guessing that the expression you are using as your “show if” condition needs to look for data in another table and that is what is failing. To be honest, however, I’m in over my head at this point. I’m hoping that @Aleksi, AppSheet’s expression wizard (who has helped me a lot – thanks again, @Aleksi!) will take a look at the expression you’re using and give us some advice.

By the way, your screenshots have helped me understand your situation much better than I understood it before but I have a suggestion that will make any future screenshots even easier to understand. First, when you take a screenshot of how something looks in your app, be sure to include the indication toward the bottom of the screen of what table the screenshot is based on. Here’s an example:

This “Data” indication is in the app editor but not in the app itself. So, a screenshot from the app editor can be more useful.

Similarly, when you show an expression, let us know where (that is, in which table) the expression is located. If I get confused about the table locations, that confusion makes it hard for me to give you a helpful answer.

Good luck!

Kirk_Masden

What is the name of the table in which

contains((sites[profile]),“04”)

is used? If you can reference a column in the same table, it should be easy. As I wrote before, I think it may be a bit harder when the condition depends on another table.

By the way, in your original explanation of the problem, “Sites” is capitalized but here it is lower case. I’m pretty sure the expressions are case sensitive, are they not? So that might be a problem.

Or, I see “Site” (not “sites”) so spelling might be a problem.

Finally, since sharing the app with me didn’t work, let me suggest another alternative. Make a copy of the app, delete any sensitive information from the tables, and then share it on your portfolio page.

To do this, you need to go to “Manage” in the AppSheet editor, then, under “Deploy” you “Deploy” your app. Then, go to “Author” (also in “Manage”) and make your app a public sample. Then if you tell us the address of your portfolio page, we can check it out.

The address of your portfolio page is

“https://www.appsheet.com/portfolio/” + your AppSheet ID. My AppSheet ID is 230844 so my portfolio page is:

https://www.appsheet.com/portfolio/230844

Denis2

The table is “Electricity Meter Readings” but I will also share the app through the portfolio page.

Many thanks,

Denis

Denis2

Kirk,

Mine is a prototype so It tells me I cannot deploy.

Denis

Steve

Neither table names nor column names are case-sensitive.

Steve

What does this mean? An “inline view” I would take to mean the view with the “_Inline” suffix. What do you mean by “set [the form view] as inline”?

Steve

In order to accomplish what you’ve described, you need an action (Behavior > Actions) of type App: go to another view within this app and a Target expression that uses LINKTOROW() and IF() to generate the deep link for the appropriate form.

Denis2

Let me try it (but tomorrow). Many thaks

Denis

Denis2

I’ll try this tomorrow but this highlights major issue with Appsheet documentation. How is a newcomer supposed to work this out??

Denis

Kirk_Masden

As I indicated above, I wound up getting in over my head on this one. So, I really appreciate @Steve coming to the rescue. I tried to engage, not because I thought I was qualified to do so but because it seemed that @Denis’s inquiry had gone unanswered. I’l bow out here but I’d like to also thank @Lynn for pitching in! Good luck @Denis!

Kirk_Masden

In regard to AppSheet documentation (@Denis’s last comment), I agree. This forum has been invaluable to me and is what has made it possible to build an app that I am now quite happy with but I’ve been rather underwhelmed by the documentation. There are quite a few documentation pages but, in my experience, they are difficult to decipher. I would like to see more example expressions with more detailed explanations for people like me who have trouble absorbing the lessons.

Speaking of lessons, I once proposed that AppSheet create a course of simple lessons that a novice could work through to become familiar with the app. That proposal didn’t get a big response but I still think it’s a good idea. The explanations could be text, but occasional video illustrations might be helpful. The lessons would be like simple recipes (i.e. “We’re going to start with these ingredients and make such-and-such.”). Ideally, they would have a cumulative effect so that a person who took the time to work through the whole series would have a good understanding of the platform. The lessons would take some time to create but save a lot of time on this forum.

Steve

Suggestions welcome!

Aleksi

Sorry to join this conversation little late… but it sounds you want to hide one of your inline views from the form view depending on the [Utility] value. When you want to do that, you need to show or hide the virtual list column from your “Sites” table.
Use something like [Utility]=“Gas” for the gas readings and [Utility]=“Electricity” for electricity readings.

Mike_A

Thanks @Aleksi. must admit i got confused what @Denis was trying to accomplish and I also though he was simply trying to hide or show the two virtual column lists based on type values. So I am still wondering why ShowIf in the two virtual columns cannot accomplish this - showing only the appropriate lists based on the value of the [utility] column.

I am not the best with the formulas yet, but should he be able to test the column value as he is trying? How to not show the list column if none of the list items equal the utility type?

Kirk_Masden

Thanks @Steve! One example of a situation in which it would be nice to see an example with more detailed explanation is

contains([profile],“04”)

that @Denis asked about. The “contains” expression can be found here:

. . . but what follows is the entirety of the explanation that is available there:

CONTAINS(text, keyword) returns TRUE if keyword is found in text.

Notice that there isn’t even an example of how the “text” is to be indicated (e.g. “sites[profile]” or “[profile]” depending on where we are in the app).

Also notice that there is no link to a more detailed explanation.

In addition to tutorials, I’d like to see a link on every one of these expressions to a separate page with examples of how the expression can be used. Obviously, creating such pages will take many hours. In the long run, however, I think such reference pages will greatly enhance the value of the AppSheet platform.

Google sheets has a huge number of users so, just as a matter of scale, it may not be practical in the short term for AppSheet to try to replicate the quality and quantity of documentation that’s available for Google sheets. Still, it would be nice to see pages like the following for AppSheet:

Aleksi

@Mike, @Denis is trying to show/hide the virtual list depending on the value in a parent record, not in the child record… If I have understood this request correctly.

Denis2

Thanks to everyone. OK, so the parent table is SItes and the Child (reference) tables are electricity Meter Readings and Gas Meter Readings. Sites has a column “utility” that contains one of two values "electricity"or “gas”. Some rows have no gas,some have no electricity and some have both. Where only one value exists I don’t want to sow the two forms “electricity meter reads” and “gas meter reads” but only one dependent upon the value in the utility column. Aleksi’s suggestion solves the problem. So, many thanks!
There is a second element to my “show-if” problem. Sites contains a column [Profile] which (a) only applies to electricity and (b) contains either “03” or “04”. My "electricity meters readings table contains two columns (1) Day Read and (2) Night Read. I only want to ask or data entry for Night Read where (sites[profile] = “04”.
Earlier in this thread Steve Coils suggested I needed to use Actions and a linktorow target expression but I wasn’t sure if this referred to this last problem or the former.
Many thanks for any further assistance and for all y our help so far.

Denis2

See attached screenshot to understand what I am trying to do. This doesn’t work.

Aleksi

You can read it with the Deref expression for the Show? like… [Site].[Profile]=“04” (if the Ref column’s name is “Site”).

Denis2

Many thanks, Aleksis -this works.

Aleksi

You’re welcome

Denis2

Last question – can I enforce leading zeroes in a number field (or it could be a text field)– the actual meter reading?

Denis

Aleksi

If it’s a number field, you can choose digits from column’s definition.