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.
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
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โฆ
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.
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.
Go to Table and click add new table to add you Teams
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โฆ
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)
Iโve learned to ALWAYS put the Display text in โโ, especially if there are special characters - meaning anything other than letters.
โCost (inc VAT)โ
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โฆ
If you go to Appsheet Community the have articles (and sometimes videos)
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.
Thank-you lovely AppSheet community for helping with these question:)
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?!?!
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.
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?!?!
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |