Setting up Notes

Question

I previously set up a group of related Notes tables, and it seemed to work fine, but there were a few nuances that I didn't particularly like. So I am rebuilding it from the ground up and had a few questions.FYI: Long read ahead.The way I previously set it up is the following:

3 Tables:

Notes
Note Types
Note Subjects

Relationships:

Note Types &lt; Note Subjects
Note Types &lt; Notes
Note Subjects &lt; Notes

&nbsp;Note Types table - field:

Type

Text - Multiple Choice

Note Subjects table - field:

&nbsp;Subject

Text - Multiple Choice

In the Note Type form, I can select a single type from the dropdown, then in the embedded grid I can select a subject from the dropdown for each grid row. Having a dropdown in the embedded form allowed me to relate multiple subjects to a single Type without having to manually type them in for every line. Hence the use of the Text-Multiple Choice for the Subject.In the Note Subject form, I can select one Note Type from the dropdown and one Note Subject from the dropdown.A single Note Type can have multiple Subjects related to it, eg:

Job (Type)

General
Left Message
Paint Sample
Parts
Pictures
Quality Control
etc
etc

However, a single Note Subject can be used in many different Note Types, eg:

General (Subject)

Assignment
Client
Contact
General
Job
Location
etc
etc

Also, in the Notes table, the Subject field is conditional on the Type field, such that when I select a Type, it only allows me to pick from the related subjects for that type.I have thought about a number of different ways to accomplish the desired outcome, being that I have a Note Type and Note Subject in the Notes form with the subject being conditional of the type, but to do it in a cleaner manner (aka Best Practice). I have been kind of focused on Best Practice as of late and would like to ensure I am following it, not only for user experience, but for future development purposes. Just because something works now, doesn't mean it will be simple to follow in the future if this is ever handed off to someone else. I want to avoid that and ensure it is simple to follow so that development can continue if needed.I believe best practice would be the following, but not really sure:My first thought is to have the Type in the Note Types table be a Text field and input all the different types. I would also have the Subject in the Note Subject be a Text field. However, if I understand things correctly, by doing that and because a Type can have multiple Subjects, and a Subject can be used by multiple Types, I would need to establish a Many-to-Many table for the Note Types and Note Subjects. I would then need to relate that M:M table to the Notes table and set the conditional parameters .....&nbsp;Any thoughts/ideas on the above, would it work, is there a better way to go about doing this? Is the way I originally set it up perfectly acceptable (I guess that might be subjective)? Suggestions? Recommendations?------------------------------Andrewandrew.fry25@gmail.com------------------------------

blakeharrison · Answer

Andrew -You're correct in thinking to make the Type and Subject fields Text. Since these are their own tables, the use of a Text-Multiple Choice is redundant and will only cause more issues later. The structure of this setup doesn't need to be super complex, but you are right in thinking you will need a Many to Many table for the Types/Subjects. I would suggest a structure like this:
The arrows in the diagram represent the direction in which you will need to setup the Parent/Child structure, with the points indicating the Child table. For this to work well, you will want to setup the reference field for the Type Subjects table to be conditional on the reference field for the Types table. You can do this using the Conditional Values section in the field properties here:

Once you have the structure updated and the values set in the various tables, you should be good to go!

------------------------------Blake Harrison - DataBlender.ioQuick Base Solution Provider------------------------------

andrewfry · Answer

Blake,I really appreciate the simpleness with which you described everything and the images you shared for clarification. Thank you!I believe I have my table relationships set up correctly, but just want to be sure.Here is what I have for my Note Type Subjects (NTS):

Here is what I have for my notes table:

I already had all my Note Types and Note Subjects input into their corresponding tables, as text, before creating the M:M NTS table.Would I be correct in understanding that I need to edit each Note Type to associate it with the various Note Subjects, to have the Note Type to Subject relation appear in the NTS table, and then in the Notes form drop downs?Still working on getting the correct field adjusted for the conditional options.Thanks for your insight!------------------------------Andrew------------------------------

blakeharrison · Answer

Andrew -The relationships look right for your NTS table, but the Notes table should have 2 relationships - one to NTS and one to Note Types. It looks like that exists on the NTS list of relationships, so I'm hoping the image just cut off the NTS&lt;Notes relationship.Anyhow, as far as data goes, you'll have your Types and Subjects in separate tables (which it sounds like you've already got). In the NTS table, you will need to create records for each combination of Type &amp; Subject that is appropriate. This table should probably only have the reference fields and maybe a Notes field for identifying anything special a user may need to know about selecting that record. When I do something like this, I often put in something like a Use Case so the user can better understand when to select a particular record.The Notes table will then have 2 drop-downs. One for the Note Type and then a 2nd for the NTS. The relationship with the NTS table is where you'll set up your conditional so that only the NTS records associated with the same Note Type are shown in the drop-down when a user is creating a Note record.I hope that clears up any confusion.------------------------------Blake Harrison - DataBlender.ioQuick Base Solution Provider------------------------------

andrewfry · Answer

Yes, the Notes has the 2 relationships. I just cut off the NTS&lt;Notes relationship from the screenshot as it was already in the prior screenshot.So I have the tables set up as I indicated above and have done some formating of the reports, forms, etc., as well as done some testing to ensure that everything is working as desired.Currently in the Note Types form, I have a single field: Type, and in the Note Subjects form I have a single field: Subject.When I input a new Type or Subject (using the corresponding form) it shows up in the Note Type Subjects (NTS) table without any relation to a subject, or vice versa to a Type. I believe this is functioning correctly.I can use the NTS form and relate the Type with the Subject or vice versa, but it only allows me to relate one type to one subject at a time. They are both drop downs. I believe this is functioning correctly, unless I am mistaken.In the Note Types form, I can add the NTS Subjects field, and make it an editable grid, thus allowing me to relate multiple Subjects to a single Type, at the same time. I can also do the same to the Note Subjects Form, so that I can relate multiple Types to a single Subject. This would be the desired way of doing it, but it requires that all types and/or subjects already be present to relate them.However, I do have 2 issues that I need to correct. I will post the 1st one here as I try to figure out the 2nd one.

Notes Form

In the Notes form, I have the Types field from the Note Types table; it is a drop down menu. This appears to be functioning correctly.However, I cannot seem to get the NTS - Note Subjects field to be the correct drop down in the form. I have not set the conditional settings yet. Once I am able to get the Subject dropdown to function correctly (show me the Subject options) then I will set the conditional settings.I wonder if I have the correct field selected for the form ... ???The available options that I believe could be applicable are:

NTS - Note Subjects_Subject

This is a TEXT (lookup) field

NTS - Related Note Subject

This is a NUMERIC (reference proxy) field

Related NTS

This is a NUMERIC (reference) field
This is also the field where I set the conditional settings.

If I have the NTS - Note Subjects_Subject field in the form, while in Edit mode, it is just a blank field, and I am unable to input any value.If I have the NTS - Related Note Subject field in the form, while in Edit mode, the field is a drop down but it only shows numerical values.If I have the Related NTS field in the form, while in Edit mode, the field is a drop down but it only shows numerical values.For these last two options, I could change the Default Record Picker settings for the NTS table, so that it shows the NTS Note Subject text instead of the NTS Note Subject ID#. But I am unsure if that is best practice or not. I feel like I am missing something here that should be obvious.------------------------------Andrew------------------------------

andrewfry · Answer

After posting my previous response, I believe I figured out to use the Related NTS field, which means that I will need to adjust the NTS default record picker to show the Subject text, rather than the ID #.Going to give it a try ... wish me luck ...​------------------------------Andrew------------------------------

Forum Discussion

Setting up Notes

8 Replies

Related Content

April 2024 Qrew Notes

March 2024 Meetup Notes

Set Current Time?

January 24th Qrew Meetup Notes

September Meeting Notes

Recent Discussions

Save & Redirect in a Formula URL Using API_GenAddRecordForm

Many to Many too Many...

Selecting which fields to display from Formula URL searches

Bulk create child records from selection list

(Pipelines) Get Users in Role / Process XML from HTTP API