Connecting Tables to get a consolidated report

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have two tables one is Single Expense and other is Multiple Expense both have a user field in it.The purpose of creating two different tables was if someone is incurring an expense, he/she can submit the expense with the receipt right away and for multiple expenses table is more for management who submit all the expenses at the end of the month etc. 

I want to consolidate report with a date range which should get user data from both tables if a user has submitted single expense as well as multiple expense to give me a total in the report. 

Please advise. I also have third table which is user table with User field being a unique key. 

Photo of AZ

AZ

  • 40 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 62,520 Points 50k badge 2x thumb
If you are looking for a single detail report with data from both tables, then there is not a way to do that. A detail "Table" report can only be made for a single table.
Photo of AZ

AZ

  • 40 Points
We can't even consolidate data from both tables into Summary report to say 'Adil' spent total of $3000 in June or ideally Adil spent $500 on Mileage, $2500 on office supplies with a total of $3000 in June.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,520 Points 50k badge 2x thumb
Ok, yes summary is possible.

I suggest making a Summary field called

[Mileage current month] with filters of date is during the current month and expense type = Mileage.

Then copy that field and name it

[Mileage current month -1]
Filters are
Date is during the previous month


Duplicate again with filters of
Date is during the previous 2 months
Date is not during the previous 1 month

You get the idea, just keep making summary fields.

Then if you are crafty you can get a set of fields to  calculate a month and year in words.  For example     June  2016.

Then when you lay out the fields, suppress all the field names and make a nice square with the actual months across the top row and then down the left-hand side the expense categories and in the cells you'll have all the summary fields .

The result will be that the Summerfield populate automatically and you can decide how many months back you want to see the data, for example you might want to make the last three months over me if you want to go crazy and make the last 12 or 13.    The result will be that the Summerfield populate automatically and you can decide how many months back you want to see the data, for example you might want to make the last three months over me if you want to go crazy and make the last 12 or 13 mile  but by getting the first fields right and then duplicating you can crank out the Summery fields pretty quickly.

 Of course, just to add to your misery, you're actually have to detail table so you're going to have twice the number of summary fields and then I guess if you want to consolidate at what you would actually show on the employee record would be yet another set of fields which would total of the two summary fields.

The downside of showing the total field of the two is that it denies you the opportunity to drill down into the detail.