Database type app with dynamic drop downs

[Roberto_Fernand]

I’m a newbie to app development know a tiny bit about sheets.

I want to build an app that can.

1. Insert Dental practices with their information i.e name, address, phone number - I already have a sheet with such information and view in the app in which I can add, delete and edit such information and respective sheet.

2. I also have a sheet with individual dentists information, name and surname. Each dentist works at a dental practice listed in a separate sheet as above. This list can also be edited, and deleted and had a view on the app.

3. The starting view is of the main sheet that will hold the combined information from the first two sheets plus jobs sent to the dental lab from these practices and dentists.

4. I want the user to be able to add new practices or select previously entered ones from a drop down list. Next the user either selects or ads new dentist to the corresponding practice. Next the job type is selected from provide entries on a drop down list or added as a new job type. Other information will include, date added, date job required, a unique ID per entry and also a qr code which will be printed along with chosen information.

5 once all this information has been entered it needs to be stored with a unique ID and qr code which can be used to search for the job quickly via scanner. This information will need to be editable, deletable and printable to be placed with the physical job sent from the dentist to the dental lab.

Biggest issue for me in knowing how to generate the main view which aggregates all the previous entries from practices dentists and job types.

[WillowMobileSys]

One question first…Would you consider the lab submissions to be primarily tied to a Dentist or the Dental Practice?

By the way, Welcome to the Community!!

[Roberto_Fernand]

Thanks for your reply. A lab job submission is tied both to a dental practice and a dentist.

[WillowMobileSys]

Let me ask this is a different way. If a dentist were to change Dental Practice affiliations, i.e. move from one Dental Practice to another, Would the lab submissions they were responsible for making go with them to the new facility or would they stay with the original Dental Practice?

[Roberto_Fernand]

The lab submission would stay, as the record would identify the practice, the dentist and the job details in it’s own record together with a unique ID and qr code.

[WillowMobileSys]

Ok, then that tells me the Lab Submissions would tie to the Dental Practice - though obviously the Dentist would be listed as the submitter on the Lab Submission record.

From a data structure perspective, this is how I would assemble it:

1. In your sheet or database, create 3 tables for the entities you described - Dental Practices, Dentists, Lab Submissions (or whatever names suits best). You may need other supporting tables but you’ll see that as you go. I strongly recommend including a dedicated ID column in each table. So Practice ID, Dentist ID and Lab ID

2. In Dentists, include a column for Dental Practice plus other dentist details.

3. In Lab Submissions, include a column for Dental Practice, Dentist, Type an other pertinent details.

4. In Dental Practices, DO NOT include columns for Dentist or Lab Submissions. You’ll see why in a minute. DO add columns for other practice specific details.

5. Load these tables into your AppSheet app.

6. In AppSheet go to the columns for the Dentists table and edit the Dental Practice column. Set as type = “Ref” and turn on the “Is part of” switch. This create a parent/Child relationship with Dental Practice being the parent.

7. In AppSheet go to the columns for the Lab Submissions table and edit the Dental Practice column. Set as type = “Ref” and turn on the “Is part of” switch. This create a parent/Child relationship with Dental Practice being the parent.

8. and 7) will automatically create Virtual columns in the Dental Practices table for “Related Dentists” and “Related Lab Submissions” giving access to these lists.

Now, In a Form when you are entering or editing a Dental Practice record you will be able to add one or more Dentists and/or one or more Lab Submissions to that Dental Practice record.

Basically, the point is that all three are tied together and accessible anywhere in the app.

I tried to be somewhat detailed knowing you are new to AppSheet. Does this all make sense?

[Roberto_Fernand]

Thanks so much for your suggestions. I’ll implement then tonight and let you know.

[Roberto_Fernand]

I followed your instructions (which were very clear) and it works but of course, needs more work. I’ll play around with these functionalities and get back to you if I need help.

[WillowMobileSys]

Awesome! Yes, it was only meant to establish the initial data structure. Once you have that then you can begin building the user interactive functions. Have fun and don’t hesitate to post your questions in the community when you have them.

[Roberto_Fernand]

Where do I go to get rid of the edit options but only in this view. I still want the data to be editable but only once the user guess to the second page

[Steve]

[Roberto_Fernand]

Awesome. That was easy once you how.

[Roberto_Fernand]

So now I have an app that generated information from other tables. I would like to automate a qr code generator for each new entry. I have looked on the forums and I followed both the instructions listed there, neither work. Any ideas?

[Steve]

Roberto_Fernandez:

I would like to automate a qr code generator for each new entry.

I suggest you start a new topic with this question to increase visibility.