All things TrackVia!

Read, write, comment.

This is a public Category  publicRSS

Customer Story

    • Pulling data from individual records in relational...
      Customer Story posted Jan 24 by Sam Williams
      691 Views, 10 Comments
      Title:
      Pulling data from individual records in relational database into a view or form
      Summary:

      Probably don't have the terminology correct, so I'll try to explain a bit better.

      I have two databases. Database 1 contains records with fields, from which I want to bring values into a view of database 2.  I need to be able to create a field in database 2, that will pull a value from a specific record in database 1, which has a field value that matches a choice from the drop down list in database 2.

      In other words: In database 2:

      field = (dropdown list choice from DB2 = field value from DB1 record). 

      I then need this association to enable me to bring in the values from the other fields, within the matching record.

      I hope this makes sense to someone.  I really need some help with this one.

    Comments

    • I think this exactly the question I was trying to solve.  I have two databases, one is Properties and the other is Zoning.  In Properties I want a link to Zoning which will allow me to search by city and then have a dropbox list of zonings in  that city. Seems like the same as above.

    • With TrackVia you can link fields from two dabases using the link to parent field type. 

      For example if you have two databases:

      1. Sales reps database (called the parent database) with all rep contact information.  Assume this databas includes a field called, sales rep. 

      2. Sales leads database (called the child database) with all leads.

      You can link the Sales Rep field name to the sales leads database through a link to parent field type.  You will then be presented with a drop down list for all rep names included in your reps databases. 

      Once you have linked the databases together you have the ability to incorporate any fields included in the parent database in a view created from the child database.  When you go to create your view for your sales leads database using our custom views feature you will have the option to include the parent fields in your view.  These fields are denoted in brackets: [Sales Rep].

      Let me know if this addresses your question.

    • Thanks Lee,

      As I expected, I didn't fully describe my delima. :)

      I figured out how to do the link to parent database, without any problems.  However, this doesn't actually fix my problem.  When you link to parent, as you have very effectively described, you can only see the "linked list" from trackvia's website.  I want to be able to have my users input data, using a web form and then, somehow, use a data field from the web form to link to  a data field in the linked database and pull field values from that linked record.

      It is very simple to link to parent record and pull these field values, within Trackvia.  As far as I can tell, there is no way to include the "link to parent" drop down list, in a web form.  For this reason, I am trying to figure out how to make this association, after a record has been uploaded.  The only logical way I know to link them is to use a value, from the web form, and associate/link it to a parent database field, with the same value.

      If I could figure a way to do this, and produce a hidden value in my child database, I could then use formulas to select and publish values from the parent fields.

    • Sam -

      I'll have a member of our technical support team follow up with a reply tomorrow morning regarding your desire to include a link to parent drop down list in a web form. 

    • Ed,

      I appreciate that.  It will make my life a lot easier, if I can figure out how to do that.

    • Ed,

      Thanks.  I tried that and it works, but unless I did something wrong, it did not solve my dilema.  Maybe it is better explained by saying I need a lookup function.  When inputing data we need to enter zoning classifications.  Rather than enter each manually, which is fine if it has to be that way, for consistency I would like to have the person inputing the zoning from a drop down menu.  Our problem is there are 30 cities.  If possible I would like them be able to click on the city and then choose the zoning within that city and the "zoning" data would populate. 

    • Yea!  What Lee Said!  That's exactly what I need.  A lookup function.  I think :)

    • If your zoning field is linked to another database that contains the zoning information you could use our parentlookup() function to retrieve any data from that database and insert it into a field in your other database.  For this formula you simply enter the name of the field that links to the database as the first argument and then the field you would like to retrieve from that database as the second.  For Example:  You might use a formula of parentlookup("Zone","Zone Regulations") to retrieve regulations from the Zones database and enter this into a field in your other.

    • I use a number of parent child database relations, and really like this feature.  My issue is that I want linking that works both ways.  Here is my situation, so you can understand the problem:

      I have a database called New Car Stockbook, which is inventory.  I'd like to pull a report of my unsold inventory.  Easy, right?  Keep reading...

      I have a second database, Deals, which tracks Deals from the writing stage through to delivery of the vehicle.  It is important to understand that not all deals work out- maybe 20% of deals that get written don't result in a delivery.  One of the fields in the Deals database links to the New Car Stockbook, so Deals is the child, New Car Stockbook is the parent.   Other fields in the Deals database indicate if the deal has been finalized, or when the deal dies.  

      Now the tricky bit:  I want to be able to filter my New Car Stockbook for vehicles that have not yet been rolled- so I want to know if there is any Deals records that link to the record in New Car Stockbook with specific features.  Knowing that there is a record in Deals that links isn't sufficient- It needs to be an active or a finalized deal.  I want to be able to filter parents based on their children (and, potentially, grandchildren, ect).  

    • There is a way to do this using a calculated field in the Deals database and then the childsum() formula to determine how many deals, if any, are set to "Finalized".  To do this you first would use a calculated field in Deals you could call  "Finalized Count" with a formula similar to the following:

       If(Status="Finalized", "1", "0")

      You'll have this populate by checking whatever field you use to mark a deal as finalized.  This will insert a 1 into this field for any finalized deals and then you would use a calculated field and the childsum() formula in New Car Stockbook to get a total of this field for all deals linked to this car.  Your formula for this would be similar to the following:

      childsum("Deals","Car","Finalized Count")

      In this formula Car would be the field that is used for the link from Deals to New Car Stockbook.  Then you could create a view in New Car Stockbook that is filtered to show all records where this number is less than 1 to see which cars have no finalized deals.  This could be expanded to get information about "grandchildren" as well.  If you need any help building this in your account let us know.