Problems with unstored calculation field.

[nareyramos]

Hello everyone,

 

I have three related tables: Main Menu, Activities and Line Items. In my Activities table I have some fields to describe each activity and a portal (from Line Items) to input the different tasks under each activity. Once ALL the tasks for a particular activity are completed (Line Items::Task Status), the field "Activity Status" in my Activities table changes from "Open" to "Finished" (calculation field). I also have a calculation field that assigns 1 to each open activity and 0 to each finished activity, and a summary field that adds all the 1 and 0  to determine the number of open activities at a given point. I have created a calculation field in my Main Menu which has the value of that summary field in Activities but unless I refresh the screen after every change in Activities, it just doesn't work. I have read that unstored calculation fields based on related summary fields are a problem, and that all this should be scripted, but I don't know how to do that. Any suggestions? Thanks in advance,

 

NR

[mr_vodka]

Try SUM ( LineItems::activity)

[nareyramos]

activityID and the other activity fields are not in LineItems but in the Activities table. LineItems allows me to input info about tasks that make up each activity. Therefore, I cannot SUM activities in LineItems. If a specific activity is finished (meaning all its tasks are completed), my ActivityStatus field changes from Open to Finished. This status field under Activities is the one I need to work with in order to determine the number of activities that are still open.  I guess I will try to upload a sample file to better explain myself. Thanks.

 

NR

[nareyramos]

Here it is. You can see that there is a calculation field in Activities named Open_Activity_Count that adds all the activities that are open, and a summary field named Open_Activity_Sum that gives you the total number of open activities. I want to have a field in the Main Menu table showing the total number of open activities next t the Go To Activity button but don't know how to do it. I was able to make it work with a calculation field which equals Open_Activity_Sum but doesn't work right.

 

NR

Activities&Tasks.zip

[mr_vodka]

In the main menu table you can create a calc as such:

 

ValueCount ( FilterValues ( List ( Activities::Activity_Status ); "Open" ) )

 

 

BTW, I dont understand the relations between MainMenu and Activity. If you are trying to use MainMenu as dashboard, you will need to use the Cartesian ( X ) operator instead of the = sign in your relationship key.

[nareyramos]

Wow... it works great! Thank you so much Mr. Vodka. Regarding the use of the Cartesian (X) operator, in reality I have a few global fields in my Main Menu table, and now this ValueCount field. I don't know if that makes a difference. Please let me know. Thanks again for your help. Appreciated.

 

NR