Need to create a relationship between 2 tables that.....

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I need to create a relationship between 2 tables (Buildings and Lease Agreements) based on matching city and state. I'm trying to see agreements that match on the same city and state. How can I do this?
Photo of Samuel Carroll

Samuel Carroll

  • 130 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
I'm not sure I follow your question completely, and it might just be a terminology thing.

Are you trying to create a new table to table relationship?
Trying to make the current relational drop-down conditional upon the city-state?

Do you have any more details about your tables, and what your use case or process is?
Photo of Samuel Carroll

Samuel Carroll

  • 130 Points 100 badge 2x thumb
Table 1 is SalesForce.  Table 2 is  LeaseNet.

I need:

a.       The Relationship needs to be based on matching City/State

b.      Within the SalesForce table, I want to see the Agreements that match on the same City/State.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
You do  not need to have a relationship to have a "Report Link" field on the SalesForce record.

Just create a Report Link field and specify the  [City, State] field in the Sales Force table and match to the same field in the Lease Agreement table.

You will need to create those fields in each table to match on..

for example

List(", ", [City], [State]) as you are matching on two fields, so you need to make them into one concatenated field.
Photo of Samuel Carroll

Samuel Carroll

  • 130 Points 100 badge 2x thumb
Ok I am really new to this. Kind of thrown into quickbase in 1 day.  I have created the report link fields and I need to see where the results are displayed.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
no problem, welcome to QuickBase.

After you have the two fields created for [City, State],  then on the Sales Force tale create a field of type Report Link, called perhaps "Lease Agreements for this City, State".

Configure the setup for that field by selected the City, state field on the salesforce record and then using the button to locate it's matching field.

Then put the report link field on your Salesforce record and set the FORM properties to display the results on the record.

Then you will likely want to create a report to select the fields ands the sort for that embedded report.  Create the  report of Lease agreements with NO filters (as the Report Link will do the Filtering for you), and edit the form properties to use that new report.

The only filters you would want on that report might be like "Active leases only"
Photo of OLI

OLI

  • 100 Points 100 badge 2x thumb
I have the same situation as the prior user, I have a list of outstanding projects that I would like to see in a report format. I have created the fields for the report however I am unsure as to how to link the current data of outstanding projects to be viewed in a report format. And I am also new as to QuickBase therefore I am a bit lost....
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Can you post a new question and explain your tables and if you have any relationships? 
Photo of OLI

OLI

  • 100 Points 100 badge 2x thumb
Just did!
Photo of Samuel Carroll

Samuel Carroll

  • 130 Points 100 badge 2x thumb
Hmmm. My boss seems to think the relationship is needed. :(
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
I have been doing QuickBase since 2002.  How long has your boss been using QuickBase?  Just do it my way and show him it works and he will leave you alone. :)

That is assuming that you have stated the question correctly

One way to explain that a relationship is impossible is that what if salesforce has two buildings in the same City.  Both will need to show the same embedded report of say 10 lease agreements in that city. So how would the child Lease Agreements know which is their one correct parent?

Its a common misunderstanding that you need to have a Relationship to have a Report Link.  its the opposite.  When you make a relationship, QuickBase says "would like fries with that?".  So it gives you the fries because you might like them.  But in fact you are allow to order fries at MacDonalds without ordering a hamburger.
Photo of Samuel Carroll

Samuel Carroll

  • 130 Points 100 badge 2x thumb
Great analogy.  LOL. I'll try it and see what he says.
Photo of Samuel Carroll

Samuel Carroll

  • 130 Points 100 badge 2x thumb
Report links are the more efficient way to go you are correct.  My boss was not aware of this.  However, I was not able to get them to work correctly.  I don't have the 'Only include values if they match exactly in both fields' box checked.  None of my records are showing.  
I need to see where the related agreements are with my buildings.  I'm am sure I have created the report links incorrectly.  I need the related agreements to show for a specific building.  I know there is a way to do this with report links.  I'm just way off base.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Can you tell me the exact value result on an example of a Lease Agreement record the calculated field to combine the City and state.  Then go to a building in that same city and state and tell me the exact calculated value for the city and state.   I want to see if they are the same. 
(Edited)
Photo of Samuel Carroll

Samuel Carroll

  • 130 Points 100 badge 2x thumb
The results are there are no agreements found.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
I did not ask for the result.  I know that it is not working.  I am asking you to tell me what is in the formula field you created which concatenates the city and state together.

Did you make two formula fields like this?  

List(", ", [City], [State]) 

One on each table?  

Did you use those two fields called say [City, State] when you configured the report Link field? 

Can you give me an example from records on each of your two tables (for the same City, State) - as to what that formula field calculated to?