merging tables help

Question

Greetings,needing help correcting a design flaw I made in our app. As a sub-contractor we bid jobs daily to many General Contractors. Hundreds a week. To try and segment our bids from the sales process I created two tables. Bids and Sales. An automation creates the Sales record when the Bid status is changed to 'Sales'. But, since the bid folks also handle sales it's created two sections for them to enter data into and two tables to view. I'd like to merge the two tables back into one Master table with info from both so as to have one point of reference for these employees to view. Any thoughts on an easy way to get this done? I know the calendars and dashboards will need some clean up after but I can get that done easily enough on my own. Just having a booger of a time figuring out how to get all the data on one table. Thanks in advance. -John

JustinTorrence · Answer

That's tough, because there can be many Sales to one Bid. What is your plan when there are 2 Sales for one bid? Will you have a duplicate field to hold the value, like Sale Price 1 and Sale Price 2?

If there is only ever one sale to one bid, you can use the Import feature:

Create a report in the Sales table that has all of the columns you want to copy over (make sure the "Related Bid" is one of those columns).
Use the Save as Spreadsheet function to save a CSV copy.
Rename the column headers in the CSV to their corresponding field name in the Bid table, and change Related Bid column header to Record ID#.
Save your CSV
From the App home page, click on More, then Import/Export.
Select the "Import into a table from a file" option.
Under Select Table, choose the Bid table
Under Select Merge Field choose Record ID#
Select the csv you created with the Choose File button
Click Import From File...
Look over the preview that is provided to ensure all the correct values are going into the correct columns

Sign up

Login to the community