Difficult to explain this one, but here goes...
Example Setup (simplified)
A database that tracks property owners and a portal that tracks the purchase and development of their property.
Table: "Owner", with fields:
"OwnerID" (Parent Key)
The above fields are placed in layout "OwnerView"
Related Table: ProjectProgress, with fields:
"ID_fk" (Foreign Key related to "Owner::OwnerID)
"LogDate" (auto-enter: Date of record creation)
"ProjectID" (auto-enter: Serial)
"ProjectStatus" (Populated via Value List "Milestones")
A Portal based on Table:ProjectProgress placed in layout called "OwnerView"
So ProjectProgress::ProjectStatus is populated with a Value List of milestones as the project progresses. However, within the list there are certain milestones that have more "importance" than others. And of the "important" milestones, the ones that appear later in the project timeline are more important than the previous "important" milestones.
I'd like to be able to create a script that will search all records that have an occurrence of a certain "important" milestone.
Here is a sample of the Value List "Milestones". The items in bold are "important" milestones. Items not in bold are a sort of "sub-milestones":
Approved by Jurisdiction
So with the above list, I'd like to create three buttons:
Button1: Find and display all records that contain the "important" milestone of "Negotiating" in the field "ProjectStatus".
Button2: Find and display all records that contain the "Important" milestone of "Contract Signed" in the field "ProjectStatus" (even if the records also contain "Negotiating").
Button3: Find and display all records that contain the "important" milestone of "Approved by Jurisdiction" in the field "ProjectStatus" (even if the records also contain "Negotiating" and "Contract Signed".)
My Idea was to create a new field ("Current_Status") that would somehow auto-populate with the "Negotiating" once that portal record has been created, but then re-populate to "Contract Signed" once that portal record has been created and finally, re-populate to "Approved by Jurisdiction" once that portal record has been created. This would then make the search script easy. But I can't figure out how to do that (set "Current_Status" as a calculation field with a formula that can parse the above?).
Or maybe there's a completely different/better way to achieve the above?
Thanks in advance!!
EDIT: Sorry, I just remembered that I will have other scripts that will have Open Url commands. The paths for the Open URL will be determined by the most recent "important" milestone. Meaning: if the most recent important milestone is "Contract Signed", then the path variable assigned to the Open URL command will be different than if the most recent important milestone is "Negotiating". So I'm really hoping to find a way to populate a separate field with the most recent "important" milestone so I can control the path uls in my other scripts.