Setting up Notes
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 qu...
Forum Discussion
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:
------------------------------
Blake Harrison - DataBlender.io
Quick Base Solution Provider
------------------------------
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:
------------------------------
Blake Harrison - DataBlender.io
Quick Base Solution Provider
------------------------------
- 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
------------------------------- 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<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 & 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.io
Quick Base Solution Provider
------------------------------- Yes, the Notes has the 2 relationships. I just cut off the NTS<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
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 - 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
------------------------------
