Many “Notes” Fields tied to one

Hey guys,

I’m trying to setup something that will be using the API to write the data to records, but I am trying to figure out the best way to organize the data to begin with.

Basically, I have customer records that are viewed by hotels, restaurants, bars, etc., and the venues want a way to add notes to a customer and view all the notes for that customer. Eventually I want to have permissions around who can view which notes, but for now I’m just trying to have a list of notes that ties to one field in another table.

The data is organized in Table 1 with the primary field being a member #, and one field for “Notes”. Table 2 has the primary field also by member # (really just a list of numbers though), and the columns contain repeating sets of Note+Date/Time+Author. There is no defined limit to how many individual notes (columns of 3) a member might have linked to their account.

What would be the appropriate way to link the Notes field in Table 1 to the corresponding Notes Row in Table 2, such that I could view all of the data in the row?

Thanks so much for your help,
Adam

I would have one set of columns in [Table 2], so your fields would be:

  • {Note}
  • {Date/Time}
  • {Author}
  • {Note Type}: A single select field to record whatever data was implied by having sets of the columns listed above
  • {Link to Table 1} (which will show the Member ID as that is the primary field in [Table 1])
  • {Formula} (which outputs the note with the “meta data” attatched to it, i.e. {Date/Time}&'\n'&{Note}&'\n- '&{Author})

The primary field would not be the {Link to Table 1}, and would probably be the timestamp.

Your [Table 1] could then use Lookup or Rollup fields to get all notes displayed within the customer record’s row.

Each note being its own record should make permissions easier to implement with more granular control.