I have built a people table with separate text fields for their first and family name fields.
Then created a Template field that held a concatenation of the first and family name fields.
I am beginning to suspect that the template with the concatenated fields does not equate to a text field that has the first and last name manually entered into it.
I have tried to use the template field (containing the concatenated first and last name) as the key for a relationship between the people table and a notes table for notes associated with the various people and it does not seem to create the relationship.
Any revelations on the use of a template field in this manner
Any alternate suggestions on how to create a relationship using the full name?
Can you show me your config?
Hi @MaximeBaker - I’m sorry help me understand what you mean by config…putting here a copy of the people data table showing the template field
With the template
Showing the attempt to filter based upon contents of full name
What is “Person”?
You mentioned a relation. If you are using a relation you shouldn’t need a filter. I would be interested in seeing how your relation is configured.
That’s a really bad idea, and even if it works it will be extremely fragile.
Use UserIDs (RowIDs) or email addresses to create User relations.
Hi @Darren_Murphy
Thank you - I completely agree…
I want to use row Id’s but I have been challenged as to how to do that - Does it involve creating a relationship using row id - but what does it get matched with?
Hi Jeff,
Thanks for your input
I realise they are two different methods of doing the same thing
I have tried both - neither seemed to work …
Relationship configuration here
In your Users table you have a RowID column. This becomes your UserID.
In any table that you want to relate back to the Users table, include a UserID column.
When a user adds a row to that table, write their UserID into that column.
You can then create a relation between the two tables, matching on UserID.
Relations work by matching exact values. I can’t see what is in your “Person” column in your Notes_People table, but it appears that nothing in that column matches the Full Name template column in your People table.
2 Likes
I agree with @Darren_Murphy about using the user ID from the user’s. I was going to suggest that once we figured out your relation.
Looking at your screenshots, I think the problem might be because your relation is in the wrong table. You should have a relation in the People table relating to the Notes table. When you view the details for a spec person, you can add a collection sourced from that relation.
2 Likes
Thank you so much @Jeff_Hager, @Darren_Murphy and @MaximeBaker
Firstly to @MaximeBaker 's question - Person is the key field in the “many” (the Notes) table
Secondly, following the insights and suggestions in your various comments, the relationship has been put onto the People table and there are now potentially “many” notes records pointing to their individual People records.
I also changed the way the value of “person” (in the notes table) was being set - For teh purpose of create and edit of a notes record, I made the person field a lookup based on the “Full Name” field in the People table - and the relationship now works.
Also I am now using rel_notes (the relationship source from the People table) as the source of the Notes table collection.
Now to remove the fragility and implement relationships based upon row-id and not data content.
Any sugestions of some good videos that could help me with that?
1 Like
I’m not aware of any videos that specifically cover this topic, but it is pretty straight forward.
Let’s assume that you are using a standard Form Screen for adding notes. What you will want to do is pass the signed in users UserID as a User Profile value with the form.
1 Like
Hi Darren,
I have been reflecting on this message and your much appreciated input.
Allow me to try to reflect back what I think you are saying.
- In the user table, the row-id of any user’s record in the user table is actually the user’s UserId
- If there are tables where it is necessary to access records by a specific user, then that table needs to include a USerID column (in that data table) so that a relation can be created between a user and the user’s records in the other table, and thereby creating a potential collection of records that all point to the same user record.
That would support a use case where multiple users need to be able to create multiple records that they can access using a relationship between the data record and their respective user-id.
My use. case is that of a CRM - - very few users of the system - large numbers of people records (none of whom are system users) and many individual note records llinked to people records
I don’t think putting my userid on each record is required for my use case.
The question then arises- how to use a organisation row-id as the basis for the relationship with a person, rather than the actual text field organisation name.
I understand the fragility of using a text field (ie organisation name) in a record rather than the row-id - if someone changes the organisation name the relationship will break, if not dealt with at the time
Your comments pls?
oh, I see. Sorry, I misunderstood. I assumed they were all users adding their own notes.
Okay, so it’s a similar concept. I assume that you have a table that lists all Organisations, one per row? In this table you would also add a RowID column, which would serve as the Organisation ID.
Can I just clarify - when adding notes, do you want each note associated with a Person, an Organisation, or both?
Also, what is the relationship between People and Organisations? Is it strictly one Organisation to many People, or can the same person be associated with multiple Organisations?
1 Like
Thanks Darren
yes - I have set up the Organisation table with a row-id…which I understand will serve as the Organisation id
Each note is to be associated with the person - not the organisation
The relationship for the people to organisation is each person can only be related to one organisation , however one organisation can be related to many persons.
Sorry I haven’t followed this closely, but I assume this is what you need?
Data Structure
Table: Organisations
- RowID (Primary Key): Unique identifier for each organisation (e.g.,
O001). - Name: Name of the organisation.
Table: People
- PersonID (Primary Key): Unique identifier for each person (e.g.,
P001). - Name: Name of the person.
- OrganisationID (Foreign Key): References the
RowIDin theOrganisationstable.
Table: Notes
- NoteID (Primary Key): Unique identifier for each note (e.g.,
N001). - PersonID (Foreign Key): References the
PersonIDin thePeopletable. - Content: The content of the note.
- Timestamp: The date and time when the note was created.
Sample Data
Table: Organisations
| RowID | Name |
|---|---|
| O001 | Tech Corp |
| O002 | Health Solutions |
| O003 | Green Energy |
Table: People
| PersonID | Name | OrganisationID |
|---|---|---|
| P001 | John Doe | O001 |
| P002 | Jane Smith | O002 |
| P003 | Alice Johnson | O001 |
| P004 | Bob Brown | O003 |
Table: Notes
| NoteID | PersonID | Content | Timestamp |
|---|---|---|---|
| N001 | P001 | Had a great meeting with John. | 2023-10-01 10:00:00 |
| N002 | P002 | Discussed health plans with Jane. | 2023-10-02 11:30:00 |
| N003 | P001 | Follow-up call scheduled with John. | 2023-10-03 09:15:00 |
| N004 | P003 | Alice provided updates on the project. | 2023-10-04 14:45:00 |
| N005 | P004 | Bob shared insights on renewable energy. | 2023-10-05 16:00:00 |
1 Like
ThinDinh
Thank you - you have captured the requirement perfectly and illustrated it in a way that am am going to adopt and use when planning other database structures…In fact …what has just popped into my head is to try to build an app that will support a database design.
Having said that - when I am entering a person and filling out the person creation form and I come to the drop down to choose an organisation to relate it to - Won’t I see the row ids - which will be meaningless to me as I will be wanting to choose an organisation name
So I guess the question is - what does the relationship column look like?
to have the row-id stored ior the organisation as the foreign key stored in the person record …but, also still allowing for the display of the organisation name on the screen
…Thought bubble…Insight… - Ahhh - its by using a lookup for the organisation name - and that field is what is used in the choice field but then the row id is stored in the relation field…
It would be great f you could show me an example if the relationship definition and the columns needed for this with organisation and person
