I have a table of Projects.
Each Project has the following fields (for this example, they can be checkbox fields)
Requested by/Submitted to Customer
In Preparation
Submitted to HNC
Approved by HNC
Executed
Disapproved/Cancelled
In Progress
*Multiple checkboxes may be true for any given record.
Additionally there is a multiple Choice that labels each record as: RSO, ESO, Urgent, or Long Lead (only one selection possible), and a Total Cost field.
The goal is to make a table or summary report that looks like below:
Because a single record may fit into multiple categories, it is stumping me. My only current idea is to use a helper table that has 7 records (corresponding to each SO Category). Then use formula queries to get the all the numbers, so in total 5 formula queries (RSO, ESO, Urgent, Long Lead, and Total Cost). I dont anticiapte more than say, 1000 projects so I don't think the formula queries will be too memory intensive, but I also try to stay away from them as much as possible since they can crash apps.
Alternately, perhaps I can make 7 relationships from the helper table to the Projects table, where my key field in the helper table is the SO Category (and instead of checkboxes I use formula fields that simply state the text of the Category if it applies), then use summary fields. But 7 relationships seems a bit excessive.
Any better ideas?
------------------------------
Mike Tamoush
------------------------------
