Sergio,
I'm not sure if you'd be open to a coded approach, but if so, here are my two cents. I have a relatively complicated solution for you, but I've used it a few times and it works. I'll spare you the lines of code right now since there may well be a simpler way to implement this, but in general here is my approach that I adapted from another post I found in this community a while back (I believe one of Dan Diebolt's master creations):
- Table Setup
- Create a relationship between Campaigns and Contracts with [Related Campaign] as the related field. It should be either a Text or Numeric field, not a formula.
- Create another table for storing records ID's temporarily, called User Focus. Make the key field be the type User and have another field called [Campaign ID].
- Add a Formula - User field in Contracts called [Current User] with the formula "User()" which just inserts the username of the person currently logged in.
- Create a relationship between User Focus (parent) and Contracts (child) with [Current User] as the related field. Add a lookup for {Campaign ID] down to Contracts.
- Functionality
- Create a Formula Rich Text button on your Campaign form that will save your new Campaign record (otherwise you won't have a Record ID# to work with), then saves the Record ID# to the record associated with your user record in the User Focus table. If the current user is not already a record in User Focus, use the code to tell the system to add a record and populate the current user into the key field.
- Have that button (or a second one) redirect you to a table report of your Contracts (fully searchable and filterable to help with the list getting long). It would be best for that report to have a filter turned on to only view Contracts that are not currently assigned to a Campaign.
- On your Contracts report, build in a Formula Rich Text or Formula URL button field called [Add to Campaign] that either directly copies the value in the lookup field [Campaign ID] into [Related Campaign], or it can check a box in the record to trigger an automation to copy the [Campaign ID] value. The button can then refresh the page and let you click the same button on other contracts until all the contracts you want have been added.
There are some more features you can build in if you want to get fancy, like having the Contracts report show up in a pop-up that can close when you click a "Done Adding" button and refresh your Campaign page, but you are starting to get into more complex code at that point.
This post can get you pointed in the right direction, and if you search for "User Focus" in the community, many other similar posts will appear.
https://community.quickbase.com/communities/community-home/digestviewer/viewthread?GroupId=103&MID=35790&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer------------------------------
Oana Toma
------------------------------
Original Message:
Sent: 02-07-2020 13:29
From: Sergio Sanchez
Subject: Related already created records to a Parent
Hello community. I have 2 tables: Campaigns and Contracts. My problem is:
- The campaign table is the Parent and Contracts is the child.
- I have already uploaded a lot of contract records, but none of them are related to a Campaign.
- When I am creating a Campaign, I would like to be able to select a couple of contract records and relate them to the Campaign record that I am creating.
- So, the question is: what is the easiest approach to achieve that? I mean, should I use an API, an embedded report, buttons, formulas, save and redirect... I am attaching a picture of how my form looks right now. Again, the goal here is to have uploaded childs related to a parent.
Thanks and regards,
------------------------------
Sergio Sanchez
------------------------------