Building Strong Connections with Many-to-Many in AppSheet

If you need two tables in your app to have references to each other, then you need to set up a many-to-many relationship between the tables.
In this video, you will learn how to create jnter-dependency between two tables for various use-cases. For example:
A project can have multiple members, and a member can be in multiple projects.
An order can have multiple products, and a product can be in multiple orders.
Get the AppSheet Best P4actices eBook:
bit.ly/4bh3JSp
(Thanks for your suppport. 🙏)
Building from this app, watch this next:
• Mastering AppSheet fie...
Recommended related videos:
AppSheet Best Practices
• Level Up Your App Deve...
5 Best Ways to create dropdowns
• Top 5 Ways to Make Dro...
_________________________________________
Do you need coaching and/or consultation with the app that you are building?
Book a call with me. Link found in main channel.
===========================
Video Chapters:
00:00 Types of database table relationships
00:29 Is AppSheet a Relational Database System?
00:49 What do I mean by "Full of Sheets"?
01:06 Subscriber shoutout @magnusgoransson9564
01:58 A small favor from the viewers
02:40 What is a many-to-many relationship?
03:11 Google sheet set up for a proj mgmt app
04:04 AppSheet table set up
04:36 Adding members to a Project
06:20 Why an EnumList will not work
08:00 How to properly connect projects to members
09:16 Creating a "Tasks" join table
14:25 Checking the connections
15:44 Customizing Inline Views (Child tables)
20:16 The inline view dilemma
21:04 Validating the many-to-many relationship
22:00 What's coming next?
===========================
#appsheet #buildingwithappsheet #howto #tutorial #manytomany #projectmanagement #fullofsheets

[@LionelCOSTE]

The best teacher 💪

[@FullOfSheets]

You give me too much credit; but, Thanks a lot! ☺️

[@tobiasochieng]

Excellent job

[@FullOfSheets]

Thank you! Cheers!

[@BEATWINCONTROL]

The best!!! Thanks!!🎉 where is the promised video about detail view customizations?? 😮

[@FullOfSheets]

It's coming this week. 😊 I'll give you a shoutout in the video. 🤗

[@BEATWINCONTROL]

​@@FullOfSheets awesome!! Can't wait for it!! 🎉🎉

[@FullOfSheets]

It's up now. 😊

[@user-jp88]

fantastic! thanks!

[@FullOfSheets]

Thanks bro.

[@innovatune_ai]

Thank you for the video and clear explanations. It really helps. I have subscribed and gave you a thumbs-up and will follow what is coming. Great content!

[@FullOfSheets]

Thank you very much!! Welcome to the Sheety community. 😁

[@innovatune_ai]

@@FullOfSheets Thanks!

[@FullOfSheets]

Welcome, mate!

[@fedepalma]

thanks! your content is really useful!

[@FullOfSheets]

Glad you think so!

[@user-it2cz2ve9m]

great explaination

[@FullOfSheets]

Thank you 🙏

[@gladstonycoliveira1573]

Wow!

[@FullOfSheets]

Did it help? 😁

[@mkourampa]

Excellent video. Sorry…I meant In the projects view I want to see the name of the member and not the email. How can this be done? Thank u in advance!

[@FullOfSheets]

Simply add the name of the member in the slice youbuse for the Project's child task table.

[@mkourampa]

Excellent video. In the members view I want to see the name and not the email. How can this be done? Thank u in advance!

[@FullOfSheets]

Replied in your last comment. 😊

[@MinhXuanHo-gf8mh]

Great video! Thank you for sharing. I have a question regarding choosing task ID as the primary key. By doing this, you can't avoid duplicating 2 tasks with the same name, and assigning to the same student and in the same project right ? i.e: ID | name | assign_to | project | -------------------|-------------------- |-------------------------|---------------------| TaskID_11 | planning | studentID_1 | projectID_1 | TaskID_22 | planning | studentID_1 | projectID_1 | Please correct me if im wrong. But anyways if there is such a case, would you share your opinion how to forbid such the duplication ? Thank you very much in advance.

[@FullOfSheets]

Hi, You are correct. You cannot avoid duplication of the same task to the same student by using the Task ID as the primary key. If you need to have this ensured, you have the ff. options: 1) Composite key This a combination of two fields as the primary key. In your case, a combination of the task name and the student ID. This can be done by: a) a virtual column b) a Physical column using an app formula c) a physical column using initial values The disadvantage of options a and b would be that the name and student id would not no longer be editable after the inital save. The disadvantage of option c on the other hand would be that the key would not he updated when either of the name or the student id columns are edited. - although this can be worked around by turning on the Reset on Edit option. 2) Valid If expression You would still use a combination of two columns in a helper column, probably a hidden virtual column. You then use a valid if expression to check that the helper column does not yet exist in the table. For example, let's say your helper column is called [Name_Student]. Then in the Valid If of either the [Name] or [Student ID], you use the ff expression Count( Filter("Tasks", [Name_Student]=[_THISROW].[Name_Student]) )

[@LindsayHiebert]

Excellent video, explanation and content. Great job! Very interested in reaching out to you, or members of your community for specific AppSheet project. What are the next steps?😊

[@FullOfSheets]

Much appreciated! If you need to reach out, please check the contact form in my Channel's description.

[@andyburnett4918]

Perfect timing! This is exactly what I needed to know. Now, please stop everything else you are doing and go and make the next video :-)

[@FullOfSheets]

😆 ok ok. I will release soon. 😁

[@FullOfSheets]

Continuation video already relased. Thanks.

[@andyburnett4918]

⁠@@FullOfSheetsthe only new video I see in your feed is the Context one. Can you give us the link to the continuation video?

[@FullOfSheets]

@andyburnett4918 Yes. Thst is the continuation. The Context() one starts from where we left off in the Many-to-Many video.

[@andyburnett4918]

Brilliant! I am off to watch that one right now. Many thanks.

[@GlenielGalingana]

Hi, can I request tutorial for appsheet that request for approval of 1 or more person.

[@FullOfSheets]

Can we have a tutorial where there will be an approver? #appsheet #buildingwithappsheet

[@tuby1502]

Hi I like the way you explain the various subjects in AppSheet. As my hearing is not what it used to be when I was younger I have a request. The backgound music is drowning your voice (for me) maybe is can be toned down a bit. Also the sound of your voice mike is a bit muffled. Maybe add som more "high"?

[@FullOfSheets]

Hi, Thanks for the feedback. I apologize for the music as it is quite difficult to monitor it during editing as I only use Bluetooth Headphones. Also, I use a lapel mic and I can only remove background noise. I only edit my videos via phone and there are no audio eq controls in the app I am using. Does my video captions not work for you?

[@SamHalesJr]

You should get a nicer mic!

[@FullOfSheets]

Thanks for the feedback. I do have an Audio Tecnica A2020 mic. However, I find it challenging to move freely with a big mic in front of me, even if I am using a mic flex arm. I found it easier to move using a wireless lapel mic, and the spund quality is acceptable enough for the viewer. The sound can still be improved in post-processing, but unfortunately, I don't have all the time resources to edit the video. Would you believe I actually only edit my videos in Samsung phone using a free Capcut app? 🤣 Maybe one day, if I make enough money, I can start hiring editors. But for now, I thank you for your patience in understanding the quality of my audio.

[@christianegunther6598]

The music is really distracting.

[@FullOfSheets]

Thanks for letting me know. I'll try to do something about it. This is the knly video where I uploaded thenvideo without music then only added the music later onnusing youtube studio. I'll check if there's a way I can lower down then volume.

[@FullOfSheets]

Can you check if it's better now?

[@SamHalesJr]

This could hardly be considered a many to many relationship. True many-to-many relationships result in a hidden junction table when talking about database schema. A tasks table would not be hidden and would not be considered a junction table. A many-to-many relationship would look like the multi select enum drop down but actually be references. The truth is that many-to-many relationship are not supported in AppSheet which is a unfortunate.

[@FullOfSheets]

This is true. What you described is exactly what I do back in the days with Microsoft Access. But if I remove all other task information in the #Tasks# table and only have the [Project ID] and [Member ID], then it is what you already described. But I must say, even in an Orders App where a many to many relationship is made between an #Orders# table and a #Products# table, additional columns are used in the join table for #Order Details#. This would invlude the qty, and line total for each product added in the Order form's child table.