Database/Table Design Question - Keeping Historical Notes

[mykrobinson]

I have been using AppSheet for just over a week now. I have completed the Udemy AppSheet Essentials course and am looking at some additional reference materials, trying to build and learn at the same time. All the information overload has my head spinning at this point, and I need to talk about this out loud with the hopes of bringing clarity to my own thought process, and possibly get someone else to help me figure out the best way to approach what i am looking to do.

The current project I am trying to do should be able to have a user add notes to a record. For the sake of argument, lets assume i have a list of cars that need to be worked on and currently have a cars table that details information about the cars. If user A washes a car, i'd like to add a row to a secondary table that contains the notes the user added about the job, tied back to the car along with a timestamp and a record of who added the notes.

I understand how to grab the timestamp of the when the record was created and i understand how to track the user who created the record.

What I am having trouble grasping is tying the data from two tables together. I think that if I continue to break down the Facilities Inspection app template, this will start to click for me. I'd like the end result to be something like, if I click the detail view for the car, i can see all notes that were left for that car.

As I said, i think i'll be able to get this figured out and probably just need to take a break to clear my head for a moment, but if anyone feels compelled to throw any words of wisdom, my eyes and ears are open.

If you have read this far, thank you.

[WillowMobileSys]

---

@mykrobinson wrote:

What I am having trouble grasping is tying the data from two tables together.  

---

I guess the answer to this depends on what you mean by "tying".  

If you are referring to "linking" rows together between two tables, AppSheet uses a concept very similar to relational databases.  Each table has a column set as the Key.  In the "other" related table you would insert a column of type Ref.  This Ref column contains the key value from the first table. This is very much like Foreign Keys in a database! 

You can then access column values from that first table using "dot" notation - [Ref column].[Column in other table].  

If you have another notion of "tying" please elaborate.

[mykrobinson]

Thanks for the response. Let me see if I make clarify this a bit better, I am wondering if my approach is just totally wrong. I have attached two pictures. The first is a table to receive notes from the app. The second is a client record sort of table. What i'd like to have happen is if someone edits or adds a new record and types data into the "signed off" text field, that data is logged into another table (the first image) which should capture the useremail(), now() for timestamp, the notes added, and which person that note is attached to, logged by the ID of the client being edited or added.

My thinking is that if a record is edited, that column data is replaced, so I think i need a separate table to contain historical data and be associated back to the original record.

I'm having trouble figuring out how to create an action  that allows the user to edit/add in the tradition sense but copy the desired data to another table for long term storage. Hopefully I worded that right.primary table for "current" statussecondary table for long term storage

[WillowMobileSys]

Your primary table, I'll refer to as the Visitation table.

First, I would recommend, if you haven't already, including a Patient table and a Hospital table.  The Visitation table would include Ref's to these tables.

If I understand correctly, when there is a "sign off" you want capture that in another table.  

What I would do is have a Sign Off button on the Visitation record.  When tapped, it loads the Sign Off Form. Save is clicked and then you have your data already in that other table.

To do this create an action of type "go to another view in this app".  YOu can use the LINKTOFORM() as the target.  This navigation function will allow you to pre-populate fields such as Patient_ID and any others that I bet you will decide to add.

[mykrobinson]

thank you, this makes sense. I am VERY slowly learning to think outside of the box a bit with all this. I do have a Patient table and Hospital table separated, but i like the idea of the Visitation table. Gotta work more on this modular/relational thinking for this to work in the way i would like, but your tips have given me something to focus on.

[mykrobinson]

well, i'm afraid i need some more assistance... I'll get this eventually...

I created a Sign Off action in behavior. If I understand correctly, what I have told it to do is, when this button is pressed, go to the form called _visitation_notes_Form and carry over the "patientID" from the current view. patientID is also ref'ed in the visitation form.

WHen i was first creating the behavior, the button showed up when editing a record, but it disappeared after i saved.

So as it stands, i am not able to test the behavior to see if it works as expected 🤔

[mykrobinson]

UPDATE: It looks like the button only appears if I am on the original Patient table, seems I cannot attach this behavior to a view created by a Slice.

What I am hoping to achieve is to have this button only accessible from a certain view, which is created by slicing Patients (all imaginary at the moment, no patient data is present in any of my screenshots, etc) and showing only those with a status of "In Hospital."  Once I can get that to work, i'd further like to lock down that action to only be accessible by users with a certain role. 

I realize this is all an ambitious task for a new user, but that is why I am taking VERY small steps and trying to understand more of this as I go. So now to figure out how to get this behavior/action button to actually show up in the desired view...

[mykrobinson]

@WillowMobileSys  I just wanted to drop in and thank you again. I have not come across the LINKTOFORM() function in my studies so far and likely would not have learned this for a LONG time at the rate I'm going. But your suggestion helped push me to check some other documentation about that function and get this working.

Now if I can just get that action to show on the desired view created by Slice, i will be well on my way! At least until i hit the nest stumbling block 😂