comparing data fields

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
We are using QB as our main database to track our Project management dates. Our client uses another database. We need to have both databases show the same data. We can not do bulk imports into the Clients database, everything has to be done manually. The goal is for our team to input the dates/info into our QB system. (they are currently trying to input it into both systems at the same time)  We have a report from the clients system that is uploaded to our QB. There are at least a hundred fields that need to be compared at any given time. As an example, we have a field called address, they have a field called Street Address (both of these fields are in QB) repeat this with 100+ different fields, some are text, but most are dates. We also have 100's of projects that need to have the data compared on. Each project has all the same 100+ fields. Is there a way for QB to look at the data we have in our fields, compare it to the data that is in the Clients field and give a report of all the information that is different? We have to do this on at least a weekly (if not daily basis). Is the best method to set up a third field that has a formula if address = Street Address, return "true" else return QB data.  I would hate to have to create 100 + new fields and input a formula for each one. So I'm looking for a better, smarter way to compare the two sets of data. I don't know very much about programming QB. I can write basic formulas, but definitely want to learn more. So I would need some step by step instructions on what to do. I ultimately need a report that we can export out of QB with just the changes that need to be made manually into the clients database.
Photo of Pamela Bray

Pamela Bray

  • 272 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Just a comment.  With data, its better if there is just a single source of truth and not need to maintain two sets of data manually.

Have you considered using a sync table to bring in the data from the clients table into a table in your app, and simply use lookup fields so that your data is always equal to their data?

Similarly, if there is data where you are the source of truth, enter it in your app and sync across to a table in their system with the single source of truth for the dates.

We may want to have a discussion outside this forum for a different way to look at this problem which will be automatic.
Photo of Pamela Bray

Pamela Bray

  • 272 Points 250 badge 2x thumb
They do not use QB, so we can't sync our data into their system. Unfortunately, we are forced to manually input data into their system.There are no other options than a manual input. I do currently have their data set up on a table. (I pull an excel  report out of their system and import it to our system), so I do use lookup fields for some of the data. However our management wants our team to use our QB to input the data. So we will have to have two sets of data, there's no way around that. We want our QB data to be the source data, but we can't go through hundreds of projects and hundreds of tasks on a daily basis to see what is different between the two systems.  So we need a way to see what data has changed in QB, compare it to the data on the table of the imported data and show the differences, so that we can have a data entry person manually change it in the Clients system without going project by project, task by task.  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Does their database and your QuickBase have a Key field in common such as a Customer number?  You can do a sync with a CSV file, if they can give you excel dumps to CSV on a regular basis or if they can drop them in DropBox, Box or Google drive.
Photo of Pamela Bray

Pamela Bray

  • 272 Points 250 badge 2x thumb
Yes, we do have a common number that is used, it is unique to each project.  We have to pull the data out of their system as needed. It can be pulled out in CSV or excel format. They will not provide anything except limited access to their system. Seeing that we have to do it, I just import the file into a table in our QB. Either I have to import it into QB or upload it to box and then wait for the sync, so it makes more sense for me to just import it. We use sync on other tables for other data. But I don't see how it would work in this instance. I do not want the data in their system overwriting what is in QB. We need to have our QB data be the source data, but we can't upload into their system. We have to manually enter it field by field, that 's why we need to see just the differences. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Well my only suggestion is to sync in their data, use lookup to bring those 100 lookup fields.

Then have a very long if statement to point of errors 

List("\n",

IF([field 1] <> [field 1 lookup],"Field 1"),
IF([address 1] <. [Address 1 lookup]),"Address 1"),

etc.

Then run a report where the warning message is not blank.  The warning message will tell you which field needs to be updated in their system.

I assume your goal is to update their system, as otherwise you would be able to easily upload their data into your fields.
Photo of Pamela Bray

Pamela Bray

  • 272 Points 250 badge 2x thumb
thanks, I'm not that good at formulas, so writing one long one sounds like a recipe for disaster and prone to a LOT of human error. Even breaking it up into multiple formulas based on tasks by department seems overwhelming.  

Your goal assumption is correct. We need to update their system based off our information. 

Any other thoughts or suggestions as to how we can compare the two data sets?
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
You don't happen to be in the Telecom industry?  I've seen this similar request a few different times, and it has always been with telecom.

You'd probably want some type of script to run a quick comparison to the data sets, but you'd still need to map them out at some point.
Photo of Pamela Bray

Pamela Bray

  • 272 Points 250 badge 2x thumb
Yes, definitely Telecom. Has a solution ever been devised? I'm not familiar with scripts, so is there a book or website you recommend to learn how to write them?
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I've built a few apps that do the connection automatically.  Dealing with PACE or Verizon has some tricky requirements. 

Send me an email and I can show you a few things that might help.

matthew@cirrusops.com 
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I should clarify 'automatically'. It's about as automatic as the 'parent' company will allow.

And believe it or not, but there are zero scripts running in the app.
(Edited)