Work through a sequence of tables

[JamesWB]

Hi, sorry if this is a stupid question, I've tried searching quite hard but can't fund the exact answer.

I have three tables on a sheet, Site, Machine and Hazard.

I want to start the app showing Site and then when you click on a Site, it displays the Machines for that site. When you click on a Machine, you get a list of Hazards for it.

How do you generally do this, is this achieved with Actions and if so, which Action should I be using?

many thanks for any help.

JB

[graham_howe]

Add link columns with expression to other filtered views.

LINKTOFILTEREDVIEW()

Constructed deep link to filtered view

[JamesWB]

Ooo. Thanks Graham! I'll get into that.

[1minManager]

If you name the columns right Appsheet does all this for you.  Here's how I'd set them up and assumes your tables are call Site, Machine and Hazard.

Sites Table

[SiteID]

SiteCreation]

...

Machine Table

[Site]

[MachineID]

[MachineCreation]

...

Hazard Table

[Machine]

[HazardID]

[HazardCreation]

...

Doing it this way autoamtically creates the right parent/child relationships

Hope this helps

Simon@1minManager.com

[JamesWB]

Hi, thanks for this!

I can now click to display the Machines for a given Site, but not the Hazards table from a given Machine. I added a view for Hazards (it doesn't seem to add the Hazards table by default in the Primary View) but this made no difference, one can browse direct to Hazards but not get there down the route Sites >> Machines >> Hazards.

I'm obviously missing something key, when it displays the 'Customise' button to start with, I click that, is that the correct thing to do?

I note that the Hazards view has a chart icon, but if I make it a Deck it makes no difference to the functionality.

James

[1minManager]

Its hard to understand what the issue is.  But it sounds as simple as the link between the Machine and Hazard table isn't setup right.  Can you send a screenshot of the Data => Columns section of the App for both the Machine & Hazard tables?

[JamesWB]

By the way, this was after I put some test data in it, assumed that the ID fields are intended to be keys, I numbered them sequentially starting with 1. For the creation phase, is it better to not put test data in the fields? Thanks.

[1minManager]

Hi James

For the key columns just use uniqueID() as the inital value

[graham_howe]

One thing to note is that if you already have data in the table then adding uniqueID() as an initial value expression will not update those existing rows. What I use in such situations is a script in Google sheets which added a function to generate a unique ID. This is the script:

/**
 * Calculate Universally Unique ID (RFC1422 compliant)
 * @return {string} Universally Unique Identifier Value
 */
function UUID(){
   var chars = '0123456789aAbBcCdDeEfFgGhHjJkKmMnNpPqQrRsStTvVwWxXyYzZ'.split('');
   var uuid = [], rnd = Math.random, r;
   uuid[8] = uuid[13] = uuid[18] = uuid[23] = '-';
   uuid[14] = '4'; // version 4

   for (var i = 0; i < 36; i++) {
  	if (!uuid[i]) {
     	    r = 0 | rnd()*16;
     	    uuid[i] = chars[(i == 19) ? (r & 0x3) | 0x8 : r & 0xf];
  	}
   }
   return uuid.join('').substring(0, 8);
}

To use the function I simply put =uuid() as a formula in a spreadsheet cell, let it calculate and then convert the cell back to a value for synchronisation back to AppSheet.

[JamesWB]

I've been reading more about the uniqueID() value, this creates a unique string, should this always be type Text?

[graham_howe]

Yes, the return value for uniqueID() is text.

[graham_howe]

As @1minManager mentioned, the views should work automatically but only if the tables and names are set up precisely. Are your tables (not views) called Site, Machine, Hazard and are your fields set up as Simon described? It could well be that you have one of the names slightly different and therefore the automatic relationships have not been generated.

Update to say that doing this after data was put in is not an issue, but if table and column names have changed you will need to regenerate.

[JamesWB]

Got it working now, the issue was that the data was in it. I called the table Sites (with the plural 's') as instructed in your original posting, but that doesn't seem to matter, the important thing appears to be the names of the columns, thanks for pointing that out so clearly.

What did you intended by the xxxCreation field names please? Did you mean those to be for putting in date/times of creation?

thanks!

[1minManager]

Hi James

So for every table in every App I normally have the following columns at the start:

Sites Table

[SiteID]  /  TEXT  /  InitalValue=UniqueID()  /  Key Column  /  Hidden

The key column, which must be unique

[SiteCreation]  /  DateTime  /  InitalValue=Now()  /  Hidden

Used to store the creation date of the record.  Best to record this from the outset incase you need it later on.  But also can be used as an alternative to [_RowNumber] for ordering records in a view.

[SiteMod]  /  ChangeTimestamp  /  Hidden

Records the last time anything in the row was modified.  Soemtimes useful if you're trying to isolate an issue where some data has changed in a table

[SiteUser]  /  Email  /   InitalValue=UserEmail()  /  Hidden

Records who created the record.  As with the above 2 items, handy for debugging and you might need this later.

[Report]  /  Text  /  Hidden

Used as a place to trigger Bots etc.  So you create a action that sets Site[Report]="Send Report" then another that clears it with Site[Report]="".  In the workflow the trigger is then [Report]="Send Report".

[ForceUpdate]  /  DateTime  /  Hidden

This is a new one I've only just started using.  When your just starting out it's common to use virtual columns to perform instant recalculations.  Particually on subtables such as by doing something like SUM([Related Machines].[Hours Worked]).  But when the parent table has 5000+ rows, virtual columns can increase the sync time ALOT.  As it has do this equation 5000+ times every sync 😳  So to get around using a virutal column, what I'd do is to have SUM([Related Machines].[Hours Worked]) set as the inital value in a 'normal' column in the parent table.  Then I create an action that simply sets [ForceUpdate] to Now().  Next I call this action using an "Execute an Action on a set of rows" so that I can call the action from the subtable.  Finally in the form for the subtable I set the "Event Actions - Form Saved" to be this "Execute..." action.  So bascially this does exactly the same as a virtual column without the sync time issue.

[Temp1]  /  Text  /  Hidden

[Temp2]  /  Text  /  Hidden

[Temp3]  /  Text  /  Hidden

I add these as the last few columns on some tables where I know we are going to have to add more columns later on.  One big issue with Appsheet is that you're having to develop it whilst it's in use.  If you add an extra column whilst someone is using the App then 2 things might happen.  Firstly if the App doesn't see the extra column then the data possibly wont be writting in the correct columns.  So you'll have to go back in and manually cut & paste it in the right place.  Or worse they go to try and save data, the App will check and realise that the number of columns in the spreadsheet doesn't match the number in the App.  The user is now stuck and can't sync the App.  By adding these extra few columns at the end of the table you give yourself a bit of breathing space if you need to add more columns.  Since you can use these and renaming a column does not cause the sync to fail.

Hope this helps 🙂

[graham_howe]

Excellent tips there, I use most of them too, but probably some additional steps I'll take now. I do think you have a typo or cut and paste error though:

[SiteCreation]  /  DateTime  /  InitalValue=UniqueID()  /  Hidden

I assume the InitialValue should be Now() or similar for this field.

[1minManager]

Doh, youre right 🙂 Edited

[JamesWB]

Incredibly helpful, thank you for the tips.