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?
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?
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
Iโve gone ahead and shared it with you as co-author so you can see my edit.
Many thanks again,
Denis
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.
Hi @Kirk_Masden Check your "My Apps " page and scroll down to โShared Appsโ , you should see @Denis app there.
Lynn,
Thanks for trying.
Denis
@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?
Here are some screenshots. I will also post to site.
Thanks,
Denis
Hi @Denis What happens if you use 04 instead of โ04โ ?
Hi Lynn,
Makes no difference.
Denis
Kirk,
I attach some screenshots that I hope will help.
Kind regards,
Denis
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")
)
Steve,
This doesnโt make any difference (Sorry if Iโve duplicated an email).
Denis
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.
Many thanks,
So how fo I restrict/constrain vews
Denis
Thanks @Lynn! I guess the share didnโt work because I only have โDeployedโ and โPrototypeโ as visible categories.
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!
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:
The table is โElectricity Meter Readingsโ but I will also share the app through the portfolio page.
Many thanks,
Denis
Kirk,
Mine is a prototype so It tells me I cannot deploy.
Denis
Neither table names nor column names are case-sensitive.
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โ?
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.
Let me try it (but tomorrow). Many thaks
Denis
Iโll try this tomorrow but this highlights major issue with Appsheet documentation. How is a newcomer supposed to work this out??
Denis
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!
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.
Suggestions welcome!
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.
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?
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:
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.
See attached screenshot to understand what I am trying to do. This doesnโt work.
You can read it with the Deref expression for the Show? likeโฆ [Site].[Profile]=โ04โ (if the Ref columnโs name is โSiteโ).
Many thanks, Aleksis -this works.
Youโre welcome
Last question โ can I enforce leading zeroes in a number field (or it could be a text field)โ the actual meter reading?
Denis
If itโs a number field, you can choose digits from columnโs definition.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |