I am designing an application to track government programs.
As a Property Analyst, my job is to manage the Disposition process of government property. There are many steps that take place from creating a "Case", submitting that Case to the Defense Contract Management Agency (DCMA) PIEE System.
I have a limited window of time to dispose of this property, managing 4 programs with thousands of property items is not easy. Eliminating dozens of spreadsheets into one relational system is my goal.
ISSUE I AM TRYING TO RESOLVE:
A Program has a Prime Contractor - Example: Boeing is the Prime Contractor for the FA/18 Super Hornet Program.
Every government Program has multiple awarded Parent Contracts, each defining some type of work or a product.
Each Contract has multiple Contract Orders (child) attached to each Contract.
Every Contract Order will have a Contract Type designation.
I would like the process to flow like this:
User selects a Program from a drop down or pop-up field.
The selection triggers a portal and displays all the major Contracts
Selecting a Contract triggers a second portal displaying all the Contract Orders under the parent Contract.
I suppose I can build this improperly and have a calc field to concatenate both fields, but that defeats the purpose of a relational design.
The primary function of this application is to manage and compile property disposition cases into the government Procurement Integrated Enterprise Environment (PIEE).
I want my application to arrange this data for export into an Excel Spreadsheet to submit into PIEE (Spreadsheet attached)
From reading the above post, it sounds as though you are clear about what you would like to implement, and also have a good organizational sense about the objective.
What I haven't been able to tell from the above post, however, is if there is a specific task at which you feel blocked or stumped, and if so, what that is.
If there is no such blocker, my suggestions would be:
Take a few initial steps into building out the UI/UX that you have imagined, and see how it goes.
If you become blocked by not knowing how to do a specific task, formulate that into a question to share with the forum and see what responses and ideas come back.
If it starts feeling as though you are having to go to very complex effort (a subjective term, I admit), take a step back to refresh your view to see if a simpler option might become apparent. In this case, you might start a post describing what you have tried so that others can offer feedback about whether the path seems sound, or might benefit from some kind of adjustment/adaptation.
At the same time as you play around with building out the UI/UX, also spend some of your time working on the export aspect of the solution, so as not to leave that task for last.
Additional Comment:
The solution that you have described sounds like something where FileMaker can be a great fit. There is a long and successful history of using FileMaker to get a workflow beyond using a collection of numerous separate spreadsheets, and my gut feeling is that this could easily be another such case.
The only potential that I see for some slightly more advanced work (again, another subjective term), might be with the export feature. The sample template spreadsheet attached to the above post has a number of sheets, and while certainly possible to implement, doing so with a precision match to the exact same structure of the sample looks to me like it would likely require some extra effort that goes beyond a basic FileMaker export.
Thank you for the response, it does make sense to me.
Unfortunately, I am not authorized to be forthcoming on my work, but I think you understand, and I appreciate this.
I will continue to work on this and provide an update.
Not spent much time in your relationship graph, but from your description (as well as years in the DoD world), it seems - maybe overly simplistic - that a single table, would suffice for most of what you want. Almost all the data elements between programs are the same (same fields) with a fields that identify specific things like programs. A simple FIND in a list view, potentially in a sub-summary format, with card window edits of records when selecting an item from the list, would be the quick path to results.
Relational models are all well and good, but I don't see this - again from a cursory outside view - as being something that needs that complexity.
