find occurence of values table

[asadj04]

I have a table named 'Projects' and a table named 'Tasks'. Each task is related to a certain project.

Each task is assigned a priority in the 'priority' field in the 'tasks' table, it can have one of three values which are 'high', 'medium' or 'low'.

1. I want to find how many tasks are assigned a priority of high for example.

2. Then i want to display this data on another layout, so it will say something like 'There are 3 tasks with a high priority'

[Lee Smith]

You should start this by making an ERD (Entity Relationship Diagram) of your needs. If you don’t know what this is, take a look at a couple of online courses  

 

FileMaker Inc

Lynda.com (let me know if you have trouble with this link, as I post it as a member)

Databasepros

 

Sorry, some of the links didn’t work as I put them. There are a few other training sites that will help, including the application site for OmniGraffle which is what I use. 

 

HTH

 

Lee

Edited March 18, 2014 by Lee Smith
change the links

[comment]

Then i want to display this data on another layout, so it will say something like 'There are 3 tasks with a high priority'

 

Not sure what you mean by "on another layout". To calculate the number of tasks with a high priority in each Project, define a calculation field in the Projects table (result is Number) =

ValueCount ( FilterValues ( List ( Tasks::Priority ) ; "High" ) )

Then you can use this on any layout of the Projects table as a field merged inside a text object. The result if used on a layout of another table will depend on the relationship/s between that table and Projects.

[asadj04]

Thanks that worked. I created the calculation field as above.

What if i wanted to find out the total number of 'High' priority tasks across all 'Projects'.

For example:

project 1 has 3 high priority tasks

project 2 has 1 high priority tasks

project 3 has 2 high priority tasks

So there are total 6 high priority tasks.

[Kris M]

One option could be ExecuteSQL ( "SELECT COUNT ( /"TASKS::Priority/" ) FROM /"TASKS/" WHERE /"Priority/" =?" ; "" ; "" ; argument )

 

if argument = 1 then it would return count of any high priority tasks.

 

Benefits of this method are ... that it will work from any calculation context and does not need any relationships to function.

Detriments are that is is text value dependent i.e uses text literal table and field references so if the table and/or field names change the calc will break.. and running SQL count function on 20 tasks will be reasonably fast but running it on a table of 100000 tasks would be much slower.

 

Their are other ways to skin this cat.

Disclaimer : The calc might not be perfectly written and is provided to give you an idea of how it could work. You'll have to test it in your application and debug as necessary

[comment]

What if i wanted to find out the total number of 'High' priority tasks across all 'Projects'.

 

Why don't you produce a report of tasks, summarized by Priority?