Hello! I have a table includes has three fields that I am trying to report on. The fields are Text - Multiple Choice. The 3 fields include all of the same information but for different request. They hold the type of Contract that is sent to the supplier in that row. I am trying to report on how many of each type of contract gets sent. Is this possible?
Qrew LegendCan you tell us how many different contract types there are? And are they fairly stable in terms of the contract types, or do these get changed regularly? I'm trying to consider whether a brute force formula will work.
This is the list of choices and this is not often changed.
Qrew LegendSo the root cause of the problem is that the data is not what they call normalized. That means that you have one record and essentially it has the children built right into the single record.
One way would be to change your app so that you had a parent record and what you see in those sets of columns would be children. But you probably don't feel like rewriting the app.
So what I would suggest is that you create a Child table where the data in those three columns would be automatically maintained in a child table. That way you would have super flexible reporting ability with summary reports and charts over time and all kinds of cool dynamic filters available.
The child table, let's call it Contracts would actually only need two data entry fields. Let's also assume that the table you currently have is called Projects
[Related Project]
[Contract#] (this would only have the value of 1, 2 or 3, representing the three sets of contracts)
You would make the relationship where 1 Project has many Contracts. Then lookup all those fields down to Contracts.
The you would have a calculated field called [Contract Type] and the formula would be
Case([Workspace#],
1, [Type of Agreement],
2, [Type of Agreement2],
3, [Type of Agreement3])
Repeat for the other fields for [Workspace#], [Status], [Attorney]
The set up a Pipeline to refresh this say every night or if you like every hour.
You would set up three saved table to table copies to copy the data from the Projects table into the Contracts table and just populating the Related Project] from the [Record ID#] of the Project and the fixed value of 1, 2 or 3, filtered for each respective Project where there is data in that set of data. ie if the project has no Workspace2, then don't create a child record.
Then, and I can help you with the easy syntax if you get that far, you would have the pipeline purge the child table with a one line easy API call, with something called "Method" set to the drop down choice of POST
https://mycompany.quickbase.com/db/xxxxxxxxx?a=API_PurgeRecords
and then run the three saved table to table imports, each being basically one easy line of "code" in he Make Request" Quickbase channel that would look like this:
https://mycompany.quickbase.com/db/xxxxxxxxx?a=API_RunImport&ID=11
So just 4 Pipeline steps.
Like I said that will allow tremendous flexibility in reporting without needing to rewrite your contracts data entry process into a child table. The children will be maintained and your users get to keep their same familiar user interface on the Project record.
