Forum Discussion

SamanthaStanley's avatar
SamanthaStanley
Qrew Member
4 years ago

Formula/Relationships

I have 2 tables I am trying to cross reference. 

Table 1 - Accounts - lets say this table has account names and dates the request was submitted
Table 2 - End of Day Report - This report has the account names and dates that were submitted with even more information. I want a formula that will reference table one and match them up based on account name and submitted date. 

Not sure if it matters - I tried to set up table to table relationships and it seems like I would have to manually match each account up. These reports will be imported daily and can have approx. 600 accounts on them. It would be to time consuming to match each one.

------------------------------
Samantha Stanley
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    Can you tell us if in table 1 the Account name is unique?  Or are there duplicates there?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • SamanthaStanley's avatar
      SamanthaStanley
      Qrew Member
      4 years ago
      There may be duplicates

      ------------------------------
      Samantha Stanley
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        Hmmm,

        If you were wanting to link these up manually and you had infinite manpower or infinite woman power (which is probably 2x infinite, how would you match them up?) 

        Would you be OK to simply make a report link field so that when you viewed a single record in table 1 it would show a embedded report of all the matching records in table 2? If you want that then it is easy. 

        Just make a formula field on table 1 which would concatenate the account name and the date field together and then make a similar field on table 2. Then set up a report Link field on table 1 and match up those two fields Field Properties  configuration and then put that new field on the form and set the form properties to display those records directly on the form.

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------