re: Multi Layered Calculation

[Noonshade]

Hi there,

I would love some assistance with trying to get a count of type working.

To give a context.

I have 3 tables - Module, Lesson & Lesson Deeper.

I would like to be able to show the status of my progress in the lesson deeper level, and then to be able to count on each of the other levels also. Each of the layers is effected automatically by what is triggered on the Lesson Deeper level, and has 3 responses - In Progress, Test It & Complete.

The concept that I have tried is built on keeping the Status in a separate table, and then calculation in a related table, then using a summary field to count the related fields. I am not sure how to put this into the right context in my situation and to get the desired results.

Can you assist - or put me in the right direction?

Kind regards

Noonshade

[comment]

Your description is not clear enough. What is the relationship between Module, Lesson and Lesson Deeper? Are there several lessons in a module and several "Lesson Deepers" in a lesson - or something else?

And what exactly do you want to calculate? Suppose there are 2 modules, each has 3 lessons and each lesson has 3 sections - and in each of those lessons one section is "In Progress", one is "Test It" and one "Complete". What should the result - or results - be?

[Noonshade]

Hi Comment,

thanks for replying!

The result should be a count of each of the levels of the data - showing, as an example, 3 In progress, 2 Test, 1 complete (on deeper level, and on other levels above it reflecting automatically ... )

Then on lessons it would show the count of the level of the lesson plus other lessons - and if they are not all complete, then set the field to in Progress, or alternatively, set the progress to complete - once it is done.

think of a book that has chapters and subchapters.

Module level - in progress / complete (based on below lesson being complete)

Lesson level - in progress / complete (based on below deeper lesson being complete)

Lesson deeper - in progress / complete / test it (based on self)

Counters for each to show progress and where it is up to. Hope this explains it, or if you need me to explain it a bit better please let me know.

If what I am suggesting is over-complicating the issue, please let me know. Just a simple of count of the ones in progress in ones complete on each of the levels would suffice and then they are manually loaded in between.

Kind regards

Noonshade

[comment]

On the Lessons level, you could use a calculation =

ValueCount ( FilterValues ( "Complete" ; List ( Lesson deeper::Status ) ) )

to count the completed "sub-lessons".





Using the same principle,

Count ( Lesson deeper::LessonID ) = ValueCount ( FilterValues ( "Complete" ; List ( Lesson deeper::Status ) ) )

will return 1 (True) if the lesson is complete (i.e. all its "sub-lessons" are completed).

[Noonshade]

Hi Comment,

Thanks for your reply!

Which table would those calculations be put at?

The Lesson Deeper level - or the Lesson Level?

I put the second one on the Lesson Deeper Level, and that returns a 1 when it equals the value - and 0 if it doesn't (so it is a boolean equation)

How do I then count those?

Thank you Comment - I think you have almost shown me it ... : - )

[Noonshade]

Hi Comment,

Actually - I just tried creating a summary field in that same table, that did a Total of cCountComplete (using the Calculation you provided)

and it has got the right answer

now I just need to get the Layout to refresh itself so that the counter moves when I change a record and I am done.

will try and put this in the context of Module also and see if I can get counters for each of the options, based on the different levels

Thanks Comment! Geez you are good! I will come back to let you know if I have gotten it for the other levels also

Kind regards

Noonshade

[comment]

I am not sure summary fields are suitable here, because they summarize the found set.

The calculations above would be in the Lessons table, and you can use =

Sum ( Lessons::cComplete )

in the Modules table.

[Noonshade]

Hi Comment,

Ok other levels are done ... thanks Comment!

I didn't think it was that easy! Now is it possible to set the Lesson values, based on the Lesson Deeper values?

Would it be something like if Count(Lesson Deeper: Count) > Lesson Deeper:ID; 'In Progress'; 'Complete'

Kind regards

Noonshade

[comment]

Now is it possible to set the Lesson values, based on the Lesson Deeper values?

I am not sure we are on the same page here. All this time I was speaking about calculating the status of a Lesson, "based on the Lesson Deeper values". The status of a sub-lesson does not need to be calculated - it is whatever the user enters. The status of a module can be calculated from the status of its lessons, as shown in my previous post.

Another way to approach this would be to calculate a binary status in the Lesson Deeper table, cComplete (result is Number ) =

Status = "Complete"

Then, in the Lesson table, cComplete =

Count ( LessonDeeper::LessonID ) = Sum ( LessonDeeper::cComplete )

and a similar thing in the Module table.