AnsweredAssumed Answered

Filter a portal based on two criteria

Question asked by davidcom on Jan 1, 2019
Latest reply on Jan 2, 2019 by davidcom

I’m a newbie here, and I’ve got a question that is complicated to explain, so I’ll try my best to make my setup clear.

 

My Setup

I've got three tables with relationships:

 

1st Table: "Owners" - has fields for:

     OwnerID_pk

     OwnerID_fk

     LastName

     FirstName

     PropertyAddress

 

2nd Table: "Milestones" - has field for:

     MilestoneID_pk

     MilestoneID_fk

     CreationDate

     Owner_Milestone

 

3rd Table: "Projects" - has fields for

     ProjectID_pk

     ProjectID_fk

     CreationDate

 

Relationships established:

“Projects::ProjectID_pk---<=>---Owners::OwnerID_fk”

“Owners::ID_pk---<=>---Milestones:MilestoneID_fk”

 

On a layout based on the table “Projects”, I’ve got a various portals:

 

PortalA is a single-row portal that shows property owner information from the table “Owners” (Unique Owner ID number, First Name, Last Name, Address, etc.). There can be multiple property owners in any given “Project”. The PortalA shows Owner ID and info of the currently selected owner (selected from a separate list I’ve placed in the layout).

 

In PortalA, I’ve given the “OwnerID” filed an object name of “OwnerID” in the Inspector.

 

PortalB shows certain milestones we’ve reached with the specific property owner in the Project. The table for PortalB is based on the table “Milestones”. Each owner has his own milestones, so the relationship is “Owners::OwnerID_pk---<=>---Milestones:MilestoneID_fk”.

 

In the Milestones table, I’ve got a field called “Owner_Milestone” that is populated via a checkbox Value list. So when a new milestone record is created, I have a script that does this:

 

  1. Go to the Milestone layout
  2. Set a unique ID number in Milestones:ID_pk
  3. Set Milestones:ID_fk to match Owners:ID_pk
  4. Record creation date in Milestones::CreationDate
  5. Record chosen value from the checkbox value list into Milestones::Owner_Milestone

 

Ok, so everything is working great here.

 

On to my question:

 

In my Projects Layout, I have a single-row portal called PortalC based on “Milestones” with just one field: Milestones::CreationDate. And I want to filter that portal (“Filter portal records” in Portal Setup) based on the following 2 criteria

 

  1. Get the ID of the active owner shown in PortalA and apply that ID in the filter
  2. Scan through the records for “Milestones”, looking for that specific OwnerID and look for a specific text string. For example, search for “Contract Signed”

 

My end goal is have the Milestones::CreationDate field in PortalC say “Hey, I see that owner John Smith with ID 3 is the active portal row in PortalA and I’ve gone through his related records in “Milestones” and I’ve found an occurrence of “Contract Signed” and here’s the CreationDate that Milestones record was created.

 

For the above 2 criteria, I have found working calculations for each:

 

  1. Let ( $id = GetLayoutObjectAttribute ( "OwnerID"; "content" ) ; Milestones::ID_fk = $id )
  2. Milestones::Owner_Milestone = "Contract Signed"

 

Each of the above calculations work on their own, but I can’t figure out how to apply both of the criteria in the “Filter portal records” calculation.

 

I’ve tried filter calculations like:

 

Milestones::Owner_Milestone = "Contract Signed" & Let ( $id = GetLayoutObjectAttribute ( "OwnerID"; "content" ) ; Milestones::MilestoneID_fk = $id )

 

Or:

 

Let ( $id = GetLayoutObjectAttribute ( "OwnerID"; "content" ) ; Milestones::MilestoneID_fk = $id & Milestones::Owner_Milestone = "Contract Signed")

 

But none of the above work (again, I'm a newbie, so please forgive the above attempts).

 

Any ideas from the experts?

Outcomes