AnsweredAssumed Answered

Pull 'important' data from portal records

Question asked by davidcom on Dec 26, 2018
Latest reply on Dec 27, 2018 by davidcom

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:

"OwnerName"

"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":

 

Negotiating

LOI Sent

LOI Accepted

Contract Sent

Contract Signed

Engineering Complete

Application Complete

HazMat Complete

Approved by Jurisdiction

Construction Started

Grading Complete

Streets Complete

Project Complete

 

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.

Outcomes