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.
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 Agent ID||Sales Agent Name||Sales Agent Phone|
Column: “Sales Agent ID” Type =Text, Key=Yes, Initial Value = UNIQUEID()
Column: “Sales Agent Name” Type = text Label = yes
|Project ID||Project name||Sales Agent|
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|
But when you see the data in you app you will see
|Project ID||Project name||Sales Agent|
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
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
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.
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!!!
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?
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
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
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…
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.
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…
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.
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…
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!!!
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.
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…
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…
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)
If you go to Appsheet Community the have articles (and sometimes videos)