Discussions

 View Only
Expand all | Collapse all

Connecting two child databases?

  • 1.  Connecting two child databases?

    Posted 08-07-2019 23:32
    I have three tables. Canvassing Leads which creates records in Sales Appointments and Homeowner Contact details. Now I need to grab the appointment day and time from Sales Appointments and put them in to Homeowner Contact details. 

    I guess the issues is is that I don't know how to make Sales Appointments update the correct Homeowner Contact, which got it's info from Canvassing leads. Here's my relationship diagram. 




  • 2.  RE: Connecting two child databases?

    Posted 08-08-2019 00:18
    Are you sure you have that bottom relationship correct? It seems to me that Home owner contact should have many sales appointments. Your diagram shows it the other way around? Can we first sort that out?


  • 3.  RE: Connecting two child databases?

    Posted 08-08-2019 15:36
    Totally agree that Homeowner Contact Details should be connected the opposite way to Sales Appointments. What I was running in to though, is that I cannot do a combined  text field of the Appointment Date, I get this error "That summary function cannot be applied to this field..."


  • 4.  RE: Connecting two child databases?

    Posted 08-08-2019 15:46
    no problem on that summary field. 

    There are two solutions.

    Make a text field of date called [Appointment date in Text format]

    ToText([Appointment Date])

    Then do a combined text summary of that field.

    Then on the parent record turn that back into a date

    ToDate(ToText([Combined text summary of the Appointment Date field])

    You can also make what is called a reverse relationship, but maybe just go with the combined text summary field for now.


  • 5.  RE: Connecting two child databases?

    Posted 08-08-2019 16:05
    Okay, so this is what I have in a Formula - Text field on the parent record: ToDate(ToText([Combined Text Appointment Date in Text Format]))

    Error I'm getting is: "Expecting text but found date


  • 6.  RE: Connecting two child databases?

    Posted 08-08-2019 16:07
    Change the field type to formula date


  • 7.  RE: Connecting two child databases?

    Posted 08-08-2019 16:20
    OK, so that didn't give me any errors. However, it's not showing up :-\
     
    I'm not even sure where to begin debugging this. Possibly a relationship issue? 

    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1801402/RackMultipart20190808-16276-151p5ct-image_inline.png?1565281196">


  • 8.  RE: Connecting two child databases?

    Posted 08-08-2019 16:27
    I don't know if it makes a difference, but the homeowner name, address, etc are automatically being added to Homeowner Contacts from Canvassing Leads via an automation 


  • 9.  RE: Connecting two child databases?

    Posted 08-08-2019 16:29
    Did you change that relationship around so that One Homeowner Details has many Sales Appointments?  Actually, if you did do that then you just need to have a summary Maximum of the Appointment Date field and we did not need to use a combined text summary field.

    Do you have example data where the sales Appointment is linked to a Homeowner record?


  • 10.  RE: Connecting two child databases?

    Posted 08-08-2019 16:44
    Changed the relationship before I did the combined text, unfortunately the Maximum Appointment Date didn't work either. There is sample data in there, is that the second question you're asking? 

    I think I'm confused because the series of events go like this: 
    1. User inputs homeowner data to Canvassing Leads 
    2. Canvassing Leads updates Homeowner Contact Details with an automation
    3. User adds appointment data by looking up the homeowner using the Canvassing Leads data 
    4. At this point, the user would have to lookup the homeowner appointments by linking the homeowner to appointments on the Homeowner Contact Details page via a dropdown or something? Does that sound right? 

    So sorry for all of my confusion, I really appreciate your help with this (and the help you've given previously so we newbies can google it!)

    Also, PS, the three fields I would need to pull from appointments in to the homeowner details are status (multiple choice), Date (date), and time (multi select) 


  • 11.  RE: Connecting two child databases?

    Posted 08-08-2019 00:41
    Just another general note, is the table homeowner contact details?  I would think the table is homeowner with fields that have the contact information.  

    You probably could use a report link since canvasing leads is the common denominator here.


  • 12.  RE: Connecting two child databases?

    Posted 08-08-2019 15:38
    I have a report link in the Homeowner Contact Details connecting to sales appointments. Am I able to pull information from specific fields using report link? What I'm looking for is to pull a status, a date, and a time from Sales Appointments to Homeowner Contact Details. 


  • 13.  RE: Connecting two child databases?

    Posted 08-08-2019 17:13
    I would suggest that for this step

    At this point, the user would have to lookup the homeowner appointments by linking the homeowner to appointments on the Homeowner Contact Details page via a dropdown or something? Does that sound right? 

     You would launch off the Homeowner record to add a child sales appointment record.


  • 14.  RE: Connecting two child databases?

    Posted 08-08-2019 17:17
    Is there a way to automate that? I want to place restrictions to the homeowners that a user can view based on the date of the sales appointment. Homeowners are attached to a forum like thing on their accounts so users can view all of the interactions someone had with a homeowner. Not all homeowners have appointments, but all homeowners will eventually have an interaction. And some users should only be able to see homeowners that their appointments are linked to / appointments for the whole team for that day

    I'm not sure if there is a way to jump on a call with you or another consultant as well as get someone on the app to see it to help get this in place? 


  • 15.  RE: Connecting two child databases?

    Posted 08-08-2019 17:21
    I can be reached via my website at www.QuickBaseCoach.com