I have information in the audit table and I have information in the events table. I am trying to create a field in the audit table to be used on a form that allows a user to search and select events associated with an audit record. This would mean a many to many relationship is needed right? I am having trouble with all of the events showing up in that field. Does the middle table that I am calling "events and audit links" have to have all of the records pulled into that table from both sides (events and audit tables) in order for the events to show up in the field for the audit form?
If I'm understanding correctly - when you're on an audit record - you want something that allows a user to pick one or more events associated with that particular audit, and then conversely the same event may be picked/chosen on multiple audit records?
If that's correct - then yes, your best solution is the many to many table between them called something like 'Audit Events' where an Event has many Audit Events and an Audit has many Audit Events. You don't have to pull in all of the records to this table - instead you let your users add / enter each event that they want to apply to that audit one by one. There are a couple different ways to do that depending on if you're using new or old forms in QB.
When you create a relationship QB will natively create a report link for you to display/show the children records when you're on a parent form. If you're on an Audit Record - you can show that report and use the 'Add Audit Event' that is natively available which would open up a native form where the Audit is already pre-filled and the user then picks from a dropdown which event they're associating. The user then saves, and repeats that add form process for each event they're associating.
If you're still using the Legacy Forms in Quickbase - you can also use the embedded grid edit feature, where you can embed that same report link on the form and enabled it to be a grid edit report where it operates more like a grid edit/excel type row entry, where they just pick a new event in each row. It's a little quicker since you're not adding/saving each time, but the drawback is that the dropdown for events isn't as 'modern' and so you can't type search to filter the dropdown.
Hi Chayce,
I am not able to add more than one event with the look up field, and the record I added does not show on the report link field that is connected to the form also. I am not sure what I am doing wrong. Do I have to remove the 1 to many relationships also if the many to many relationship exists?
Qrew LegendTonya,
You will not be using any of the lookup fields or report link fields from the old One to Many relationship.
On the Events form, you will have a button to ADD an Audit Event and the form will have the embedded report link field which the relationship created to show the Audit Events for that Event,
then the opposite happens on the Audit record. It will have an embedded report in the form of all the Audit Events fir that Audit,
if you have existing data from your one to many relationship there is a way to do a one time copy to copy them to the new Audit Events table. Then you would delete the old child table.
Thank you! I was able to figure it out. Once I created the many to many table and relationships with the audit and events tables the fields that I needed were automatically generated.
