Getting started

David_Kirkham · 06-20-2020 07:42 AM

I’ve followed all the Udemy course on this, watched a lot of the YouTube videos and still I am struggling to get started with AppSheet… I have a simple structure in a Google Sheet of Sales Agents, Projects and Costs as three separate tables. I can not figure out how to get these related to each other in AppSheet. I have double checked my column headings, ensured related column names are the same yet AppSheet just doesn’t seem to build the relationships like the videos show. I just can’t figure out why I am finding this no code platform so hard or what i am doing wrong (as the videos do tend to move very quick when it comes down to setting the ref attributes, etc). Also there seems to be a lot of mention at the beginning about giving tables a unique ID column, yet in many of the sample data i can’t see these!!! Am i getting confused with old videos and the platform has moved on to not require these? Just need a little guideance as I don’t know where to just get started without it error’ing with the basic data setup I am trying to achieve. Any pointers from the community would be greatly appreciated.

Mark_Taylor

Hello David,

I started using this a couple months ago and when I realized how to do this it greatly helped. You want ref Columns

Using two of your tables “Sales Agents” and “Projects” let assume the following columns
With the relation ship that each Project is assigned to one Sales Agent

Sales Agents

Sales Agent ID	Sales Agent Name	Sales Agent Phone
001	Bob	555-1212

Column Settings
Column: “Sales Agent ID” Type =Text, Key=Yes, Initial Value = UNIQUEID()
Column: “Sales Agent Name” Type = text Label = yes

Projects

Project ID	Project name	Sales Agent
1001	Project A	001

Column Settings
Column: “Project ID” Type =Text, Key=Yes, Initial Value = UNIQUEID()
Column: “Project name” Type = text Label = yes
Column: “Sales Agent” Type = Ref
---- Click the edit Icon for “Sales Agent” (Little Checkbox with a pencil Icon all the way on the left when you “View Columns”) Type with Ref(if not select it) Below that you will see Source table. Here select “Sales Agent”

Now when you look at the Raw data in your google sheet for “Projects” you will see

Project ID	Project name	Sales Agent
1001	Project A	001

But when you see the data in you app you will see

Project ID	Project name	Sales Agent
1001	Project A	Bob

The columns that you selected for your Label (your can only select one) is what will be displayed for references.

I took me a few tries and a little frustration when I first started but as I started to grasp how everything related it became easier.

Good luck to you and have fun

David_Kirkham

This is what i’m trying… I just get errors… seems to be in a new column which is getting computed… i’ve tried altering this to be anything obvious… but nothing resolves when i regenerate the structure… see pic

Mark_Taylor

I think I see what is going on here

Your screen shot is of the table “Projects”.
The “Reseller ID” Column references the Table called "Resellers"

I assume you have another table called “Resellers” where the column “Reseller ID” is the key column.

In this “Projects” Table you would like to have a column that Displays the Sales Agent for a given project.

If I am correct then follow these steps and you should be golden

Change “Project ID” to Type = text and initial value = UNIQUEID()
Set “Project ID” to be your key column in the “Projects” table.
If in the “Resellers” table you made:
the “Reseller ID” column the Key and
the “Sales agent” column the Label column

then when you look at the “Projects” table in your app the “Reseller ID” column will display the value of the “Sales Agent” column from the referenced table.

Based on what I assume you are trying to accomplish the “_ComputedKey” column will not be necessary

on a side note Personnaly I would rename the Projects[Reseller ID] column to just “Reseller”. I only use the “ID” suffix to columns that function as the Key in their table and then drop the “ID” portion of the name when referencing them in other tables.

David_Kirkham

So, here’s the thing… i haven’t created the _ComputedKey column, it just appeared when i tried to connect the two tables. So no idea how I actually delete it!!!

I followed the points above and now another field has appeared in the Resellers table called Related Projects… now i had seen this appear before and then it disappeared when i tried numerous ways to get the connection to work… so now, still same error and this extra column… lols!!!

David_Kirkham

I even loaded up the Custom App for Client Expenses so that i could see ow it should be done, see it using Ref as it should… yet i’ve tried my basic Google Sheets now three times and it just messes up each time… So I can see everything you say makes sense, I can see it working in that Sample App, but still it just seems to get confused… it’s gotta be something to do with my headings in the Google Sheet throwing it off some how!!! Does looking at my basic structure help here?

Mark_Taylor

Did you set Project ID to be the Key and Project name to be the label?

Also if you make project ID to the first column instead of the second that may alleviate some of what the app is trying to do automatically for you

Mark_Taylor

Also if you click that blue checkbox to the right of the _ComputedKey you will see a option at the top to delete the column

David_Kirkham

Yes… that’s then when i regenerated and this new field appeared in Resellers???

I just realised when i did those two changes i hadn’t slid the slider for the Key field on the _ComputedKey as hadn’t realised when it had added that that it had made it the Key column… so just applied the settings again and made sure these were now both correct… both tables show now no red errors and the _ComputedKey has disappeared… but now it still shows the error about this _ComputedKey column even though it doesn’t exist?!? Related Projects column is still there though is Resellers table…

Mark_Taylor

Did you click Save in the top right of the screen?

I always have to do that to clear out my errors

I would not worry about the Related Projects. It just produces a list of all the projects that are assigned to that reseller

Appsheet always automatically created that column and you can keep, hide, or delete it at you leisure.

David_Kirkham

Gosh darnit… so i deleted the entire app again… moved my key field to the left most field on each sheet and haypresto everything is now working… so that was what it was (I’m used to making databases so thought to use the leftmost field on the sheets as the relationship to the sheet above in the logic flow… and that’s what was messing it all up…)

Thank-you so much for helping me get to the bottom of this as i just couldn’t see what was wrong with how it needed configuring yet kept throwing all these errors…

Mark_Taylor

David,

I am glad you got it working.

I know I had plenty of “oooh that what that does” moments when I started out last month, but in the end you can actually achieve some pretty cool stuff.

David_Kirkham

lols… been struggling for several days to just get anything to work… now this is cracked i’m flying already in 10-mins and almost have a prototype working from just those basic tables… can’t believe it;)

I have a lot of interesting challenges i’ve no idea how to tackle with the user authentication and what they may get to see and edit… so that will now be the next challenge…

David_Kirkham

So i’ve got four out of my five tables all connected and doing stuff… but my Team table doesn’t even appear so i can’t add it in!!! Any ideas why this isn’t even visible in the Tables view with a button to bring it in!!!

Lucinda_Mason

Have you saved and closed the tab with the AppSheet editor? I find that clears up multiple issues. I’ve had tables I recently added to my google sheet and it wouldn’t appear until I did this. If that doesn’t work, try completely closing your browser.

Mark_Taylor

Go to Table and click add new table to add you Teams

David_Kirkham

Thank-you Lucinda, yes I closed the tabs and browser down and restarted… Now the Teams tables has appeared;) You really do need to learn all these nuances… Another one I found was putting brackets in a name field caused it to dissappear. For instance I have a column called CostIncVat and wanted to display it as Cost (inc VAT) and it didn’t like that… lols…

Mark_Taylor

on your google Sheet leave it CostIncVat
in Appsheet View columns click the edit icon on the left for the column
Go down to Display.–>Display name and enter Cost (inc VAT)

Lucinda_Mason

I’ve learned to ALWAYS put the Display text in “”, especially if there are special characters - meaning anything other than letters.

“Cost (inc VAT)”

David_Kirkham

So, am I missing something when it comes to customising views? I can’t figure out where this view is so that i can edit the layout e.g. I want the team member to be above the projects. Also if i change the _ComputedName Name field it then vanishes!!! I just want this to say Name… I presume there is a way to show only certain details depending on the view you are in? I can’t seem to work this out… can anyone point me at YouTube videos or anything to help me get the views showing the data how i want it?

Sorry to keep asking stupid questions… seem to make a lot of progress then hit a brick wall…

Mark_Taylor

If you go to Appsheet Community the have articles (and sometimes videos)

Lucinda_Mason

I assume _ComputedName is a virtual column created by AppSheet. I have not tried renaming a system generated column. You could just copy the properties and make your own virtual column.

Yes, clicking on the view name SHOULD take you to that view in the editor. There is a bug that if your UX search, or table or slice, has a value in the search bar, IT WILL NOT go to the named view.

There is also a toggle at the bottom of the UX view that allows you to see and hide System generated views.

I have found that when trying to open a system generated view, it often does not! In a recent Re-post I was told it’s better to edit system generated views than create the same from scratch. Unfortunately, I have had trouble doing that in some cases simply because I couldn’t locate the corresponding view. Yes, little quirks - possible due to my lack of knowledge.

Keep asking questions, no question is too basic. Watch old Office Hours. Read as much documentation as possible, but be aware some is old though all say edited in last week.

David_Kirkham

Thank-you lovely AppSheet community for helping with these question:)

David_Kirkham

OK AppSheet community… can you answer me this then? Similar to above… (single screen shot as limted t o only be able to upload a single image;))

I want to change this name but can’t find anywhere to change it. Based on the tips above I would have thought that the Display Name in the Project_Details UX view would use this Display Name… but as you can see 2) doesn’t get shown anywhere on in any view!!! Or happy for it to not deplay at all to save screen real estate…
How can you control these in the UX? I have the correct order on the actual Costs page (as controlled that with the Column Order, but this hasn’t rippled up a level to this view?!?!

Lynn

Hi @David_Kirkham
Check out system Ref views under view system views and find the _Inline view for your ref view. These pics are in the opposite order. Syart with the bottom one.

David_Kirkham

Great thank-you… so this has got me half way there…

The Column Order is now fixed with that setting selected to sort the order out.

BUT the Display Name is having no effect?!?!