Filtering to pull a report avoiding duplicate information
Hi, I have a table with a project's ID, Title, Type of Publication, Version #, Push Month, Push Year.
For every version we push, we create a new record. Version 1 (v1_00) can be a push for the content, the next Version (v2_00) can be a push for animation, and the following version (v3_00) can be a push for content and/or animation and so forth.
I am trying to pull up a report which shows each ID's "latest" "push month and year" from content or animation, or both.
The issue I am having is if v1 is content, v2 is animation, and v3 is content again. I want the report to show only v2 and v3 - the latest content and the latest animation push. I do not want v1 to show on my report, which, in this case, was the first time the content was pushed.
Is there a native way of doing this with filters and formulas in a report? Can someone help? Thank you.
So this is completely doable, but it would help to know how your tables are structured. The way you described your setup makes me think you're tracking all of this data in a single "Projects Table".
If that's the case, Start by changing your table structure as outlined in the Italicized Section below.
It sounds like a "Push" should be a separate table related to "Projects" where a Project can have many "Pushes". It also sounds like Project ID, Title, and Publication are unique to the Project, and Version #, Push Month, and Push Year are fields that should be moved to the "Push" Table.
The benefit of doing this is that you can then summarize the latest Push record information up to the Related Project, than send it back down to each new Push you create to Drive a Formula that triggers whether or not a push record is "equal to" the latest "Push" of any given Record Type, which you can then use as a Filter in your reports, ignoring past iterations of a content type. Here's a quick breakdown of how your app should be structured to make this happen. ***I would suggest making a copy of your current app before proceeding! If your current projects table has multiple Records with different "Push" information, but duplicate Project ID's, What you're tracking on this table is actually Push information, not Projects.
First, Run a List all Report with no filters, and be sure that report includes any columns/fields that will belong to the project, ie, project's ID, Title, Type of Publication. Once you have this report up, (Assuming Project ID should be your unique identifier for Projects) hover over the Project ID column heading and a small arrow should appear in the top-right of that column heading box. Click this arrow and choose the option "New Table Based on this Field..."
On the next screen, choose the other fields you want to be part of a Single Project Record, such as Title, and Type of Publication. Click Next...
On the next screen, Quick Base will outline what this change will result in. It should say that your 3 selected fields will become a new table, (with Project ID being the Key) and although the data will be moved, it will still relate to the records in the current table.
Press continue.
Name this New Table Projects, and rename the Old Table "Pushes" (or whatever naming convention suits you....) Also - it wasn't clear to me what field label you used to indicate content vs animation, so for the purpose of this walkthrough, I'll refer to this as "Push Category", and we'll also assume you're capturing an actual Date field to keep this walk through brief.
- Now that we have the appropriate table structure, we can actually solve your question.
1st, edit the relationship between Projects and Pushes, and on the left side, click "Add Summary" You will want to summarize the Maximum Push Date when a "Push Category" is equal to "Content", Name this field Latest Content Push Date, then create another summary field the same way, only this will be for "Push Category" Animation....(call this Latest Animation Push Date" Before leaving this relationship - we want to send those new fields BACK DOWN as lookup fields so we can use them on the Push table... We now have a date field on both the project, and any related Push records showing when the last animation and content pushes occurred. The last step is to create a Simple Formula Check Box field called something like "Is Latest Push"... Edit this fields properties and write the following formula to set the conditions for when this box should " Be Checked", note that "Push Date" is the actual date field on any given push record. if([Push Date]=[Latest Content Push Date] or ([Latest Animation Push Date],true,false)
Any Time you Create a new Push Record, this formula should check the box on the new record, effectively replacing the previous record of that "Push Category" as the most recent.
All you need to do now is add this "Is Latest" check box as a filter when creating reports on your pushes table (and just for good measure I'd set the report to Sort and Group by Project), and you should be good to go!
I know this was wordy, but it took 10 times longer to write this than it would to do it, lol! Let me know if you need more help!
