Google AppSheet allows you to create an app from a Google Sheets spreadsheet. You can also create it without the spreadsheet by going to appsheet.com directly. My first step when starting a new app from Google Sheets is to add the additional tabs in my spreadsheet as data tables. I then want to indicate which column in each table matches the data in another table to connect them. When doing this I have the option to checkbox in AppSheet Is a Part of. What is “is a part of?”
Try This Sample
I have some sample tables in a Google Sheets spreadsheet to track checking out technology devices to students. Make a copy.
Referencing Tables
In Google Sheets create each table of data on a separate sheet. Be sure to include column headers. Tables that will reference each other need to have a common piece of data to indicate how they are connected. For example, if you have a table of student names you will want to have the key be their student ID number. If you are checking out devices to students you will have a table that indicates which student is checking out the device. You can simply reference the ID number to look up the students name and other information from the student table.
Use REF type for Connecting Data
In my example I have 3 tables: students, Devices, Checkout. Students have a “Student ID” and the Devices table has a “Device ID” for each available device. The 3rd table tracks which student checks out which device. I need to reference the student table and the Devices table for each checkout.
The 2nd icon on the left side of AppSheet shows your tables. Click the plus icon on the data tables to ensure you have all 3 tables since by default only the first sheet is imported. On the Devices table, look for the “StudentID” and “DeviceID” data fields. You want to change the type to be “Ref.”
Setting Up the Ref
Changing the data type should pop open an options window. If not, click the pencil to the left of the field row. In this example the DeviceID will be referencing the Devices table. Select “Devices” from the Source table options.
AppSheet Is a Part Of Checkbox
One of the options when setting up the reference is to checkbox “Is a part of?”. The description in pop up window is:
These rows will be considered ‘part of’ the referenced table. They can be added as line items in the form view of the referenced table, and will be deleted if the referenced row is deleted (these deletes will not trigger workflow rules).
But what does that mean?
In AppSheet, the “Is a part of” option creates a special type of relationship between two tables. Let’s break down what that means:
Imagine this scenario:
You have an app to track orders. You have one table for “Customers” and another for “Orders”. Each customer can have multiple orders.
How “Is a part of” works:
- Ownership: When you check “Is a part of” in the “Orders” table (referencing the “Customers” table), it means each order is intrinsically linked to a specific customer. An order cannot exist without a customer.
- Line Items: In the app, when you view a customer’s details, you’ll see their orders listed as line items. This makes it easy to manage all orders associated with that customer.
- Automatic Deletion: If you delete a customer, all their associated orders will be automatically deleted. This ensures data integrity and prevents “orphan” orders.
- No Workflow Triggers: This is an important note. While deleting a customer deletes their orders, this action will not trigger any workflow rules you might have set up (e.g., sending an email notification when an order is deleted).
In simpler terms:
Think of it like a parent-child relationship. The “parent” is the customer, and the “child” is the order. The child is “part of” the parent.
Why use “Is a part of”:
- Data organization: It helps structure your data logically and clearly.
- User experience: It simplifies data entry and viewing by grouping related information.
- Data integrity: It enforces referential integrity by ensuring that related data is kept consistent.
Should We Use "Is a Part Of" for The Example?
Good question, if you were to check out a device to a student that device were to be removed because it is old, do you want to remove that the checkout ever existed?
Now, here’s where “Is a part of” gets a bit tricky in this specific scenario. It’s tempting to think that “Checkout” should be “part of” either “students” or “Devices”. However, a checkout record really depends on both a student and a device.
Here’s why using “Is a part of” for “Checkout” might not be ideal:
- Data Integrity: If “Checkout” is “part of” “Students”, deleting a student would delete their checkout records. This means you lose the history of which devices they checked out. The same issue arises if “Checkout” is “part of” “Devices”.
- Logical Relationship: A checkout isn’t truly “owned” by either the student or the device. It represents a temporary association between the two.
A better approach:
Instead of “Is a part of”, use regular references (Ref) in your “Checkout” table:
- StudentID (Ref): This column references the “Students” table, linking each checkout record to a specific student.
- DeviceID (Ref): This column references the “Devices” table, linking each checkout record to a specific device.
How this works:
- Clear Links: Your “Checkout” table clearly shows which student has checked out which device.
- Data Preservation: Deleting a student or a device won’t automatically delete the checkout records. You retain the checkout history.
- Flexibility: You can easily add more details to the “Checkout” table, like checkout date, due date, and return date.
AppSheet Is a Part Of:
While “Is a part of” is useful for parent-child relationships, your device checkout app requires a more balanced relationship between the three tables. Using references (Ref) provides the flexibility and data integrity you need for this scenario.
Would I Ever Use "Is a Part Of"?
Scenario:
Imagine you’re building an app to manage student enrollment in different classes. You have two tables:
- Students: Contains information about each student (name, grade, etc.)
- Enrollments: Tracks which student is enrolled in which class.
How “Is a part of” works:
- Linking Enrollments to Students: In the “Enrollments” table, you’d choose “Is a part of” and reference the “Students” table. This means each enrollment record must be associated with a specific student. An enrollment cannot exist without a student.
- Viewing Enrollments: When you view a student’s profile in the app, you’ll see their enrollments listed as line items, clearly showing all the classes they’re taking.
- Automatic Deletion: If you delete a student from the system (perhaps they’ve graduated or transferred), all their associated enrollment records will be automatically deleted. This keeps your data clean and accurate.
- No Workflow Triggers: Important to remember, deleting a student and their associated enrollments won’t trigger any workflows. So, if you have a workflow set up to notify teachers when a student is removed from their class, it won’t be activated in this case.
Think of it like this:
A student “owns” their enrollments. The enrollment is “part of” the student’s overall academic record.
Why use “Is a part of” in this case:
- Clear Organization: It structures your data in a way that makes sense. You can easily see which students are in which classes.
- Efficient Management: It simplifies enrollment tracking and helps avoid errors.
- Data Consistency: It ensures that enrollment data is always linked to a student, preventing inconsistencies.
This is just one example. You could use “Is a part of” in many other school-related scenarios, such as linking assignments to students, tracking parent-teacher communication, or managing school inventory.
