I have a relationship calc for a portal that will show either all products, or all products pertaining to another drop down of the season I am viewing.

Case ( not IsEmpty ( Settings::gCheckBox ) ; Project::cProductID_ListSeason //occurrence filtered by season selector ; Project::cProductID_ListLocal //all products occurrence )

This all works fine, but I'd like to update the Project::cProductID_ListSeason to also show products that have 0 seasons assigned to them.

My entities are:

Projects <- Products <-> AssignedSeasons <- Seasons

What would be a good approach to gather ProductIDs that contain no SeasonIDs and combine them into the list of products that do have SeasonIDs?

I want to add new products with no season while I'm filtered, then apply the season to them, without have to show all...assign, then filter by season again. Hope that makes sense 

Edited March 29, 20178 yr by shredded

Hi Shredded,

 

One option would be to add another field as a check or radio button to the layout to determine whether you want to see the 0 seasons or not and refine you calculation field based on this newly added field.

 

I hope this would help.

 

Regards,

This is what I did that seems to work fine. The only thing is that Im listing a lot of values, even though the relationship filters the correct ProductIDs. Would there be a performance increase if the calculation also determined the relationship, rather than through a table connection? If so, how can I also include Products::_fkParentID to Parent::__ParentID

Let(
    [
        KeyA = SettingsGlobal::gSelect_Season;
        KeyB = GetValue( Products::gSeasonSelectKey; 3 );
        KeyC = GetValue( Products::gSeasonSelectKey; 4 )
    ];
    If(
        not IsEmpty( SettingsGlobal::gSelect_ShowCurrentChoice );
        Case(
            not PatternCount( SettingsGlobal::gSelect_Season; "Collection" );
            ExecuteSQL(
                "SELECT \"__ProductID\" FROM Products WHERE cSeasonStart = ? OR cSeasonStart IS NULL";
                "";
                "";
                KeyA
            );
            PatternCount( SettingsGlobal::gSelect_Season; "Collection" );
            ExecuteSQL(
                "SELECT \"__ProductID\" FROM Products WHERE cSeasonStart = ? OR cSeasonStart = ? OR cSeasonStart IS NULL";
                "";
                "";
                KeyC;
                KeyB
            );
        );
        ExecuteSQL( "SELECT \"__ProductID\" FROM Products "; ""; "" )
    )
)

 

Edited April 5, 20178 yr by Lee Smith
formatted the calc

As of my knowledge, working through relationship has less negative impact on the performance than calculation fields.