comparing data fields

Question

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) &nbsp;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. &nbsp;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.&nbsp;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.

quickbasecoachd · Answer

Just a comment. &nbsp;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.

pamelabray · Answer

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.&nbsp;I do currently have their data set up on a table. (I pull an excel &nbsp;report out of their system and import it to our system), so&nbsp;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. &nbsp;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. &nbsp;

quickbasecoachd · Answer

Does their database and your QuickBase have a Key field in common such as a Customer number? &nbsp;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.

pamelabray · Answer

Yes, we do have a common number that is used, it is unique to each project. &nbsp;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.&nbsp;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.&nbsp;

quickbasecoachd · Answer

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&nbsp;List("
",IF([field 1] &lt;&gt; [field 1 lookup],"Field 1"),IF([address 1] &lt;. [Address 1 lookup]),"Address 1"),etc.Then run a report where the warning message is not blank. &nbsp;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.

Forum Discussion

comparing data fields

Related Content

Lookup Field not pulling Data

Lookup Field Not Pulling Data

Jinja Expression Comparing Now to Now+Hours

Setting Up Master Data App for App Iterations

Snapshot Fields