using existing and new data in connected tables

Hi there - I’m struggling to work out how to link two sets of data when in all circumstances one piece may be new data. For example:

  • I run repair events. Every person who comes to my event may have been before, or they may be new. I’m assuming I need to set up my visitors as one sheet, so I can pull out existing details where a person has been before, but also so I can capture details on their first visit.

  • Each of these people will bring in one or more items to be repaired at each event. Each of those items needs a unique and chronological ID. Where existing customers arrive, it would be great to see the history of what they have brought in before. For those on their first time, there is obviously no history so I will need to capture data for that item. Again I assume this item data will sit in a separate sheet.

What I’d like to do is have a form that shows visitor and corresponding item details. I’d like to be able to select a visitor name in the form if they have been before (selecting from my visitor sheet) - and for that to bring up all their details plus a list of and details of any items they have brought in before. But I also need to be able to type in new names where these are new people.

Equally I’d like to be able to type in an item ID and get up all the relevant visitor and items details, but also be able to add new items where needed.

I get how you can set a field to collect data from another sheet. What I’m struggling with is how you set a field to be both a look up field and a data entry field. Is there a way to achieve this? I’ve browsed a range of templates looking for inspiration but not hit on the right one as yet. Any support much appreciated.


It seems like you will need at least three sheets to start: clients, items, and repair requests.

The repair requests sheet is used for recording repair entries, just like how a sales order form would work but for repairs. On that sheet, you can apply link & load relationships to link to the clients sheet. This would allow you to select a client and load other related field values on the repair requests sheet. Similarly, you can also apply a link and load relationship to the items sheet so you can select an item and populate other related field values.

Once those two link and load relationships are set up, you can then go back to the clients sheet or the items sheet to create reference subtables to reference the repair requests sheet.