Portal Records Display

[Paul Hunt]

I have a gardening database, with 12 Tables. I use it to keep track of my plants, their purchase, their bloom history, their planting locations, etc.

I have the following as part of my relationships:

 

Plant has a one-to-many with Plantings - Primary Key in Plants, Foreign Key in Plantings

Plantings has a one-to-many with Locations - Primary Key in Locations, Foreign Key in Plantings

 

Plant has a one-to-many with Species - Primary Key in Species, Foreign Key in Plants

 

Species has a one-to-many with Genus - Primary Key in Genus, Foreign Key in Species

 

I want to be able to display, for example, a form with my Asters, showing what gardens I have living Asters plantings in. (Living Plantings have a blank in the field Status)

 

I created a layout based on Genus and the single record found is the Genus of Asters.

 

That layout can, and does, show me the varieties of Asters I own or have owned (Plant::Name)

 

I want to see a list of Locations for the Genus that have living plantings (one entry per location, not per planting).

 

I created a portal based on the relationship Locations. The only field in that portal is Location::Name.

 

If I do not filter the portal records, I see 3 Location names, only 2 of which have living plantings (Plantings::Status is blank). Not what I want.

 

If I  filter the portal records, (Plantings::Status (not equal) "D") I see 0 Locations, although there are 2 with living plantings (Plantings::Status is not D). Also not what I want.

 

What am I doing wrong?

 

Thanks,

Paul

[comment]

So basically you have:

Genus -< Species -< Plant -< Plantings -< Locations

and you want to filter a portal to Locations by an attribute of its parent Plantings.

For this, you have two options:

1. Place another occurrence of Plantings "behind" Locations:

Genus -< Species -< Plant -< Plantings -< Locations >- Plantings 2

and use it in your filtering expression, e.g.:

IsEmpty ( Plantings 2::Status )

2.  Define a calculation field in Locations as =

Plantings::Status

and use this field to filter your portal.

 

What you have now looks only at the first related record in Plantings from the context of Genus. So the portal will show either all related records from Locations or none - according to the status of that record. You will observe the same principle if you try to add a field from Plantings (or Plant or Species) to the portal: it too will display the same value in all portal rows.

 

Edited April 1, 2020 by comment

[Paul Hunt]

Neither option worked. Since Location has a one-to-many to Plantings, I'm not surprised. With that relation in place, whether I use the calculation field or not, I still get all locations, active and not, without filtering, and none with filtering. Thanks for trying.

 

Paul

[comment]

5 hours ago, Paul Hunt said:

Since Location has a one-to-many to Plantings, I'm not surprised.

Well, in your original post you said:

On 4/1/2020 at 4:41 AM, Paul Hunt said:

Plantings has a one-to-many with Locations

which is confusing. So I guess the correct representation is actually:

Genus -< Species -< Plant -< Plantings >- Locations   ?

In such situation, it would be difficult to use portal filtering alone to show only Locations with living plantings because a filtering expression would be evaluated from the context of Locations - and a location looks at ALL its plantings, not just those belonging to the currently viewed genus.

You could use a filtered relationship instead: define a calculation field in Plantings along the lines of:

If ( IsEmpty ( Status ) ; LocationID )

and use it as the match field to another occurrence of the Locations table. Then point your portal (and its fields) to this occurrence.

 

Edited April 8, 2020 by comment

[Paul Hunt]

Yes! Not only does it work, but it makes sense. That is, there is a link, if and only if the planting is active.

Thank you for your help.

Paul