Portal Calculations in Primary Table - Stumped

[SeanO]

Greetings. Reaching out for help as I'm staring at a brick wall currently. The more research I've done the more lost I've become.

Primary Table is a List of ALL Active Matters. Each Matter is Unique.

Portal is a List of Related Nominations. Relationship is the MatterID

Two calculations are performed for Every Matter & Displayed/Saved in the Primary Table (Save Preferable)

1.1 Count the Number of Nomimations For the Matter (SUPPORTCOUNT).
1.2 Add the Value of each Claim per Nominations (SUPPORTVALUE)

The above Calculations are for the Primary Table & Preferably a Saved Value.
The Calculations Must be part of the visible Primary Row. eg:

MATTER (PRIMARY)
MATTERID - NOMDATE - MATTER - SUPPORTCOUNT - SUPPORTVALUE - APPLICANT - APPLICANT VALUE ...

NOMINATIONS (PORTAL)
MATTERID - NOMDATE - NOMINATIONNAME - REFERENCE - VALUE ...

The final version of the above, includes a Nomination Date in both The Primary Table and the Portal. If the Nomination Date is Blank, then the Count Calculation needs to Exclude that record from the Count total.

To further confuse matters, the Applicant in the Primary Table, is also a Nominator (Primary Nominator - can be only One) and may not give their Support, in which case NOMDATE would be Blank and would be Excluded from the SUPPORTCOUNT Total.

I readily admit I'm missing something basic but I'm too deep in the woods now to count the trees. Happy to give progress feedback. Many thanks in advance for your assistance.

[comment]

I am afraid this is rather confusing. If you want to count the related records, you can use the Count() function to count some field in the related table that cannot be empty - for example:

Count ( Nominations::MatterID )

 

6 hours ago, SeanO said:

If the Nomination Date is Blank, then the Count Calculation needs to Exclude that record from the Count total.

Well, then count the Nomination Date field instead. Blank fields are not included in the count.

 

I am afraid that's all I understood from your post.

 

[SeanO]

Many thanks @commentfor your reply. I had successfully used the Count() function but on the wrong field. My new working version is:  (The Case() function is counting the Primary Table value if relevant)

Count ( Nominations::DateNomination) + Case ( DateNom ≠ ""; 1; DateNom= ""; 0; )

Just to explain further - I tried to make my original post as detailed as possible but I neglected to add the purpose

There are Two Fields to be calculated and displayed on the Primary Table list.

Namely: 1: No of Votes (Support) and 2: Total Sum of ALL Claim Value (Currency)

My Approach to developing this was to first get a simple version of each working before applying the full logic to the formula (unnecessary complications) 1 = Count (working) 2 = SUM (Not Working)

The final formula has the following logic behind it (Not FM script)

On Primary Table

1: Count WHERE MATTER/NOMINATIONDATE is <> "" OR IF MATTER/NOMINATIONDATE(Get(CurrentDate)>0)

This will return the number of nominations/support we have for the Matter. The two parts needed for the above logic are a:) The NominationDate Must not be Blank and b:) The NominationDate, based on Today's Date is not Overdue. We also need count the Primary Table Value if it is relevant using the same criterion

2: Sum NominationValue Where the (qualifying criterion) NominationDate is NOT blank or Not Expired (BTW: I have been unsuccessful at getting any sum variant to work here - including just counting all the NominationValue without applying any logic, which tells me I'm approaching this incorrectly)

If my explanation is still terrible just shout and I will rework the description. This is what happens when we stare at the problem for too long. I've been staring at this for just on a week now and my programmatic imagination has become warped lol.

[SeanO]

With regards to the second calculation: Sum ...

Sum (Case ( Nominations::DateNomination ≠ ""; Nominations::CreditorClaim; Nominations::DateNomination = ""; 0;0)) + Case ( DateNom ≠ ""; ApplicantClaim; DateNom = ""; 0;0)

The second part of the formula works correctly -> + Case ( DateNom ≠ ""; ApplicantClaim; DateNom = ""; 0;0)

This second part returns the correct value whether the date value is empty or not. 0 if DateNom is empty else Value of ApplicantClaim if a date exists.

The first part (values from the portal rows) just does not work -> Sum (Case ( Nominations::DateNomination ≠ ""; Nominations::CreditorClaim; Nominations::DateNomination = ""; 0;0))

If every DateNomination has a date then the calculation performs correctly.

If the First Portal record does not have a DateNomination then it return a "?"

If any other Portal Record does not have a date value then it returns a 

If Primary Record has no Date & ALL Portal records have a Date then the calculation works perfectly

If Primary Record has no date & First Portal Record has no Date then the result is just a blank.

If Primary Record has no Date & First Portal Record HAS a Date & any/all other Records have no dates then the calculation adds everything, excepting the primary record for an incorrect value.

My conclusion is Case() is NOT applying the logic to each related portal record individually.

I have tried the exact same scenario using if() in place of Case() with the same result. I have also tried adding and removing the default "0" in all cases. It does not make a difference.

I really need assistance with this as I am genuinely lost in this calculation

[comment]

1 hour ago, SeanO said:

My conclusion is Case() is NOT applying the logic to each related portal record individually.

That is a correct conclusion. I am afraid that's all I understood from it.

In general, if you want to count (or sum, or any other aggregate operation) related records that meet some condition, you need to add a calculation field to the related table that checks the condition and returns a corresponding result. Then apply the aggregate function to that calculation field.

For example, to sum only child amount that are above 100, you would add a cAmt calculation field to the child table =

If ( Amount > 100 ; Amount )

and then in the parent table do:

Sum ( Child::cAmt )

 

Another option is to define a summary field in the child table that totals the Amount field. Then place this field n the layout of the parent table within a one-row portal, filtered to show only records where Amount > 100. However, this is only suitable for display; you cannot use the result in additional calculations. And it could be too slow if you have many child records or if you show many parent records at the same time.

 

You could also define a recursive calculation using the While() function to go over the child records, check the condition and accumulate the result of records that pass the test.

 

Finally, to make this complete, you also have the option of making the calculation field =

If ( Amount > 100 ; ParentID )

then defining another relationship to the child table using this as the match field. This is suitable if you have many different aggregations to perform on the records that pass the same condition.

 

[SeanO]

Many thanks @comment Migraine interfering with my clarity of thought atm

Following your guidance re the calculation field for the SUM component. I will be using the value of "NominationDate" to determine whether the value field is calculated. Empty Nomination date field means that no value is copied across.

With regards the first component - DATE "NominationDate", I've changed it to check if the current date exceeds the stored "nomination date +30", in which case it is considered stale and will automatically clear the nomination date  field. Only able to set that to autorun in the layout script on record load. Haven't figured how to autocalc all portal entries without going into them individually.

I have tried  validating within the field itself in the table setup using an "IF RESULT" without success.

Would appreciate being pointed in the right direction. Again many thanks in advance.

[comment]

I am afraid that once again I have no clue what your question is. Are you trying to calculate something based on the data entered by the user? Or are you trying to modify the data entered by the user? This:

2 hours ago, SeanO said:

check if the current date exceeds the stored "nomination date +30", in which case it is considered stale and will automatically clear the nomination date  field

would certainly fall in the latter category - and I fail to see why you would need to do this. After all, a date that has expired continues to be expired for all eternity.

And of course, validating user entry is something different altogether.

 

[SeanO]

On 12/7/2020 at 2:26 PM, comment said:

. After all, a date that has expired continues to be expired for all eternity.

Hi @comment. That is only correct when dealing with static data. When dealing with live data (dynamic, changing value) it is subject to change until such time as the record is deemed closed via a different mechanism.

I explained that as "Stale Data" at which point the date record is reset - prompting a script which chases the worker to secure a new nomination - rinse repeat cycle, until such time as the business is secured or lost. This is the mechanism which determines whether the record is active or not.

If the record shows the opportunity was secured, then a script creates a new record in "actual business" and transfers the applicable captured data into this new record, eliminating recapturing the data.

If the opportunity was lost then the record is labeled as such and marked closed, removing it from the list of nominations.

I'm still trying to find a way to refresh the portal records daily and reset any calculations based on the time lapsed. This needs to be run once per day and could be an automated script. I'm just unsure how to go about this at present.

Would appreciate any guidance or feedback from anyone please as I'm unable to progress with this path. Thanking you in advance.