I have 2 tables with same fields.One are actual numbers one are goals.I need a report that compares the like fields,if they fall into the same quarter and date time frame

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

I have 2 tables with same fields.One table are actual numbers one table are goals. These are tracked by yearly quarters. one table tracks the quarters in a numeric formula field and the other in two drop down text box (one for quarter and one for year).  I need a report that compares the like fields, if they fall into the same quarter and date time frame.There is probably a simple answer but i am drawing a blank. 

Photo of Meme

Meme

  • 10 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
No problem.

Make a new table called "Budget vs Actual" and load it up with the quarters for the next 10 years on the format, perhaps YYYY-Qx

or else, you said that one of your tables already has a "numeric formula field", so maybe that formula calculates to something like 2016-1.

Anyways.... come up with a coding for the YYYYQ and load up a table and make that field the Key field.


Then on each  of your other tow tales, make a formula field to calculate to that Key field values for the YYYYQ.



Then make relationships back to each of your details table and then roll up the totals you need to the new table.
Photo of Meme

Meme

  • 10 Points
Thank you for your quick response. I have been out but I am getting stuck in making the formula field on the two other tables. What find of formula field? Numeric?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
It would be a formula text field and I will post an example of what that formula would look like today or tomorrow.
Photo of Meme

Meme

  • 10 Points
Thank you so much!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
actuallyI stared to answer your post, but I don't have enough information.

You need to build a text string by a formula text field in each of the two tables to calculate a result like 2016Q3.  Do you know how to do that based in how the data is stored in each table.

For example if you have drop downs in one table for the year and another for the quarter, then it would just be

[Year] & "Q" & [Quarter]

I do not know the fields type you are using, so if they are numeric them you will need to use


ToText([Year]) & "Q" & ToText([Quarter])
Photo of Meme

Meme

  • 10 Points
Can I please invite you to the app? I am not following.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
You can contact me via the information in my Profile.    I'm at 30,000 feet  right now in a plane with wifi :)
Photo of Meme

Meme

  • 10 Points
I sent you an invite. Whenever you have time to look, I need to pull Injury/Illness Record totals and SCA Occupational Survey table information together to compare. I have made the table OSHA/PSI Reporting table as a comparison table but I am stuck at this point.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
I made a relationship where 1 SCA Surveys have many Injuries, based on the calculated field in the format 3Q15.  You can then do any summary fields you like.  is that what you are after.  I could not understand the purpose of the middle table of the three tables, so I was not sure if there was information there which also needed to be summarized, since that table only has 1 record per quarter. ie I think that your gaols table only has  quarterly entries so the detail injuries can be summarized into the quarters, just not sure which table you want to use for your summaries.
Photo of Meme

Meme

  • 10 Points
That is exactly what i am looking for. I was trying to make it too confusing by adding another table to compare. Thank you so much for your help and have a safe trip.