Jump to content
  • 0

Comparing data from a set of 4 tables to a 5th table. From there, modifying/deleting/adding records to the 5th table based on missing information from #1-4



I'm not very good with Caspio and my developer resources are occupied with other tasks.  Trying to figure out how to do this and/or hope to find someone that could assist please even perhaps do it for me, not sure how hard this is.   Sorry if this post is a bit detailed.  I'm just stumped here on what to do.. BUT we need this to work.   Thank you for responding and reading if you are inclined to do so. Any help is greatly appreciated. 

Problem to solve-

In our business we have a set of 4 tables that we record data to each time we interact with a customer.  Each of the 4 tables represents an "encounter" with a customer of a different type.  Our customers can in one visit to us, have all 4 services or maybe only have 2. These services take place usually in the span of about 2 to 5 days, but each of the encounter/services that are related to each other, are CODED with a unique encounter number.  Our customers come back typically every week and could have all 4 services or 3 or...  Just depends.  Again the encounters, could span a two week period, but typically in 95% of the cases all fall within one week of a Monday thru a Saturday.  They are coded for the encounter ID based on which services are related to that encounter.  So an encounter is initiated and then closed.  Again typically within a week but NOT always.  Each encounter is time stamped/day date and coded with a unique visit id.  So for one week for example customer "A" could have all 4 services with an encounter ID of "1234X"  Next week Customer "A" could return and have only 2 services with an encounter ID of "1453z" for example.  But each week during their interactions with us we produce these records into these 4 tables for the different things we offer.  Again all with unique customer number/id, encounter id's and some other information regarding those encounters in the records in the table.  Hope that makes sense..  We then take those encounters based on the week, and populate a 5th table as below.  Usually on Sunday of that week.

The 5th table- (billing file table) 

The 5th table is generated from a series of reports we run to create it.  We run these reports weekly usually on a Saturday evening or Sunday for that prior week.  This 5th table that is the combined data of the other 4.  This 5th table is generated by us based on running a series of reports from the 4 tables, with some conditional logic we apply.   This conditional logic "omits" intentionally some of the records in the 4 tables when it pulls to the 5th one.   We then take the information in the 5th table and go through that manually and make small modifications at times, and then we transmit the modified information in the 5th table to our billing company.

One problem-

Remember our encounters have unique ids.  So it is possible that an encounter can have one record or two in one week, then another record or two in the next week.  BUT when we are looking at these by week, we would not know that per se.   We can surely (and do) look at the data, and see that this customer A only has 2 of the 4 typical encounter records.  BUT we don't know if the other two were omitted because of our conditional logic intentionally OR if the other two fell in the prior week.  We look at the data in the billing file table and then modify this at times and transmit that data to the billing company. So we have now two files with data- 1) the 5th table with the encounters created based on conditional logic, and a file we sent to the billing company which is then loaded into another table (6th table  (FILES SENT TO BILLING COMPANY).   At times we intentionally omit SOME of the files in the 5th table (billing file) from some of the files in the 6th table (Files sent to billing company) for different reasons based on a review of the data for one reason or another.  

What we want to accomplish-

WE want an easy way to do the following and hopefully this can be done is Caspio within a datapage.  My consultant tells me he can't figure out how easily in Caspio and wants us to use a local excel file using v lookup functions.  This maybe easier from a data manipulation point of view but in this way i cannot simply easily task someone to review and modify the data.

We want a report/datapage that an administrator from our end can look at and compare the data from the 5th and 6th tables and see what we IN the original billing file and what was NOT sent to the billing company, for whatever reason.  This would show ONLY that information that was IN the 5th table but NOT in the 6th table.  Then if there was missing information in the 6th table, that operator could enter a new record, or pull the missing record into the 6th table and have the ability to change some of the information in that record prior to submitting it.

example 1) if for encounter id 12345 there were 4 records in the 5th table (billing file) and only 3 record in the 6th table (billing file sent to billing company) it would ONLY show the missing record.

example 2) If the records in the 5th table matched the records in the 6th table (i.e. records created equal records sent) then NOTHING would show in this report for that encounter number 
"123455" etc.

example 3) If the records in the 5th table matched the records in the 6th table (i.e. records created equal records sent) BUT There were differences in the data on 2 or 3 fields it would show the data in the 5th table (billing file) and the data in the 6th table (billing file sent) and highlight the anomalies, and allow the admin to either append the dates/information or ignore the changes and then resolve this by ignoring it.


thanks for reviewing this.. ugh.. 

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...