One to Many Relationship: Adding new record vs. Selecting Existing Record

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have two Tables: Hosting Requests and Video Files

In the Hosting Requests table, the user can create a request to host a video file.

What I would like, is to have two options for them at the same time:

1. Add a new video file (which would open a window where they complete a video file form).

2. Select an existing video file (which would also open a new window where they could select (from a drop down list) an existing video file.) They could click this button as many times as they would need to select all of the video files they want to host.

I would then like a summary report that lists all of the video files associated with this hosting request.

(These video files can be hosed in many places, which is why there could be more than one Hosting Request for each video.)

I have set up a 1:many relationship (1 Hosting Request to Many Video Files) which has accomplished #1 above. How do I create a button to do #2 above?

Thank you!!

Photo of Karen


  • 222 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
So 1 Hosting Request will have Many Video File Requests, but also 1 Video file will have Many Video File Requests.

This is the classic need for a Many to Many relationship.

There is an explanation here

and there are also apps in the exchange which have examples if you search on Many to Many.

But all you need ot do is to break your existing relationship and start over by first make a middle join Table called "Video File Requests". Initially you do not need to set up any fields in this middle table.

So 1 Video file will have many Video File requests, so set up that relationship.

Then make a relationship where 1 Hosting Request will also have many Video file requests.  That middle table will essentially have the line items on the Hosting Request "Order" similar to an Invoice or Order having Many Master file items listed on it.

When you get that built, then use lookup fields ot bring down the name of the video files down to that middle "join" table and also the identifier for the Hosting Request.

On the Hosting request form, put the report link field from the relationship on the form and set it to show the children video file requests right on the form.