Read-only data form and unbound fields

[matthewmoran]

Fairly new to AppSheet. Lots of experience with Google Apps Script, SQL, Power Platform, and more.

Question:
How does one build an app that only uses a sheet for lookup data - no records are created or saved. It is simply a guided window/app to view existing data and a form to provide some calculation results.

I'm building a small app (1 view) that will read data from a sheet primarily for cascading lookups. The users will not be adding or editing rows.

Fields in sheet: Region, Site, EmployeeType, Rate

View will display:
-Region (drop down)
-Site (drop down based on the region)
-EmployeeType (drop down based on the Region & Site)
*Rate will not be displayed

I'll provide two text/numeric entry fields - though the data will not be stored. Those fields are:
- Contract Weeks
- Weekly Hours

Once they enter the weeks and hours, it will calculate an amount based on:
- (Rate * Weekly Hours) * Contract Week

I'm struggling with understanding how to create a form when the underlying data is read-only. AppSheet gives an error message. However, the data is only meant to create drop-down list that are populated only by prior drop-down list selection. Additionally, how do I create two textboxes that are not bound to any data?

I have already created drop-down list that work based on prior selections. But that was with editable data.

This is, of course, SUPER simple to do if I code the HTML myself or if I use Powerapps. I can add fields that are unbound and attach actions to those fields. Hmm...

Any guidance is appreciated.

Thanks,
MM

[WillowMobileSys]

AppSheet does not have temporary storage locations.  So, we need to store the lookup criteria and values you intend to use for calculations in table columns somewhere.  The best approach is to create a simple "utility" table (or more) with columns for each of the temporary data needed. 

The good news is that you can create this table with only a single row and simply re-use that row on each usage of the Lookup Form.   The Form View has a Row Key property for this purpose.  In the utility table row, assign a key value to the key column (rest of the columns blank) then use this key in the Row Key property.  Each time the Form is opened it will re-use the single row from the utility table.  To have the Form appear to re-start from scratch each time, set the column "Reset on edit" flags.  Of course you will also want to establish the dropdown values and configuration for each of the lookup columns.

Once this is established then you can build out the rest of the app normally to navigate and display the resultant data.

I hope this helps!

[matthewmoran]

Hi @WillowMobileSys.

Thank you for your response. I already have a matrix sheet set up and working. Column, left to right, creates a contingent dropdown field. 

The bigger issue is that, for this solution. no data is meant to be stored. It is basically a calculator of sorts. However, I have to, in effect, setup a table for resulting data in order to create a form.

This seems remarkably limited to me. I built the entire solution in PowerApps between 9am and noon yesterday using the exact same matrix table above. No data needs to be stored and an HTML formatted email can be generated and sent to a distribution list.

I'm surprised that AppSheet - this many years in development - does not provide the ability to create unbound fields and forms that can then be tied to automation and/or some type of scripting option.

From an organizational standpoint, I am part of a team tasked with promoting greater adoption of AppSheet across thousands of users. If they cannot start providing some of these basics, this is going to be a slog!

[AleksiAlkio]

Another workaround could be Usersettings. It doesn't save any value to your data source, only in user's device. Though you can have max 10 columns with it.

[matthewmoran]

I'll look into it.

[WillowMobileSys]

---

@matthewmoran wrote:

This seems remarkably limited to me.

---

Whether some entered data is stored in memory only (unbound) or placed into physical storage (bound), it still has to be stored.  The fact that all of AppSheet data is intended to be bound is not limiting - maybe inconvenient.  It doesn't prevent a capability...just that it may take an extra step or two to accomplish it by way of setting up the bound storage.

I am not sure why AppSheet originally chose to not provide unbound data concepts except that it would add a fairly significant additional data layer into the AppSheet platform. 

So far, I have not found it a major hindrance to any of the apps I have built.  But there are occasions where it definitely would be helpful and possibly open up the potential for another set of creative features in our apps.

[matthewmoran]

I view limiting as the burden generated to create solutions - not necessarily whether X can be done.

Having built solutions: in the past 60 days using PowerApps, Google Apps Scripts, Airtable, VBA, Power Automate, and MS Access - AppSheet is limiting in comparison.

I've had multiple discussions with Airtable developers, they are similar baffled that a platform that uses javascript for its functionality does not have front-end javascript widgets or true form designer capabilities. Libraries exist.

Maybe my expectations are too high but I believe Google could marshal the resources to provide a more intuitive form designer  - at minimum. By now, they could have a graphical data design tool, especially for sheet. Also, Google Apps Script integration should be able to return data, in the form of arrays or JSON objects.

If this is to be their flagship no-code platform, they'd better figure some of these basics pretty quickly.