N Amount of objects (as N amount of record ID#s) for the same project, need to select a specific one. Connected table.

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

Hello! First, my most sincere thanks for the time taken to read/think/propose any solution.

I have a table that is connected to Salesforce.com.  The information to bring from a Salesforce.com "Opportunity" into QB are products. One opportunity can have N amount of products. 

The connected table holds all the products and one of the fields (not key field) is the Opportunity ID#. Therefore, in QB table it can have :

Record Id# 222, Opportunity ID: 123, product name: Server     Ship date: 12/31/2014

Record Id# 289, Opportunity ID: 123, product name: Monitors  Ship date: 04/22/2015

Record Id# 443, Opportunity ID: 123. product name: Keyboards Ship date: 02/14/2015

I need to go through all records in this connected table and check for Opportunity ID: 123 and select the earliest ship date and also the latest ship date. 

Preferably a date formula field for each Record Id# that search the table itself to find matching Opportunity ID and then compare the Ship dates and bring the earliest or latest ship date.

Is this possible? Can this done via relationship by creating another table and do lookup or summary?

Photo of Ricardo Tsai

Ricardo Tsai

  • 40 Points

Posted 4 years ago

  • 0
  • 1
Yes, you will need to have a table of Opportunity IDs where the Opportunity ID# is set to the be the key field.  To get that table populated with the unique Opportunities ID's, there are a couple of ways.

One way is to make a summary report groups Opportunity ID and then use the More button to copy to another table. The first time it will create all the unique Opportunity IDs.

The second time it will only create as many as are needed to be added.

It may also be possible to make a butotn to do that copy using API_RunImport but I did not test that.

Once you have that table, then its a simple matter to make a Relations ship where 1 Opportunity ID has many Products and to use the button on the Relationship to Create Summary fields of the Max or Min ship dates.