Referencing table rows in multiple other tables

[Jeremy_Reece]

I’m not sure how to explain this easily to be able to search it properly. I apologize if it’s been answered, but I couldn’t find anything in the good bit of searching I’ve done, probably because I’m not searching correct terminology or explanation.

I’m working (trying to in my spare time) on a fairly in depth field service app and I want to be able to add multiple notes to different tables. Example: I’ll have tables for Customers, Vendors, Employees, Machines, Service Cases, Work Orders and probably more. For each of these I want to be able to add multiple separate notes, that can be viewed in relation to the specific related tab le data. I could add multiple notes for each employee, for each Work Order, for each customer, etc. Essentially I need a many to many relationship for the Notes.

My question is, what is the best way to do this? Would I need a sperate column in the Notes table for each related table I want a reference to? Or is there a better/cleaner/more efficient way to do this?

[Marc_Dillon]

Jeremy_Reece:

Would I need a sperate column in the Notes table for each related table I want a reference to?

You could.

Or you could keep all reference keys in the same single column. I’ve recently set up the exact same functionality, where I only had a single column to hold the key of the related record.

You can’t use Appsheet basic reference functionality in such a case, i.e. you can’t set the column to Ref, and automatically have REF_ROWS columns in the other Tables, where everything just works easily.

I’m sure you’re aware (or at least I’m hoping so) that the key values in your Table need to be unique. Typically, they just need to be unique within their own Table. But in this case, it benefits you to ensure that they are unique across the entire app. UNIQUEID() should still work fine in this case.

1. If they’re unique across the entire app, you should still be able to use a simple REF_ROWS expression to manually create the “Related…” VCs in each parent Table. Then I think the system in-built REF_ROWS magic will still work, where you click “Add” from the inline Table, and the parent key is auto filled.

2. If you can’t guarantee that your keys will be unique across all Table, then in your Notes Table, include a [table] column as well. Then, instead of REF_ROWS VCs in your parent Table, you can use :

FILTER(
  Notes ,
  AND(
    [parent] = [_THISROW] ,
    [table] = "parent table name"
  )
)

In either case, but probably required in the 2nd case, you can build your own Action to add a new Notes record. A LINKTOFORM Action.

LINKTOFORM( "Notes_Form" , "parent" , [_THISROW] , "table" , "this table" )