Getting Record Count from related tables

[Scott Pon]

I'm trying to create a dashboard that shows how many open items per department.  I have a 3 tables, Orders (Parent Table), Assigned_to (a child table to orders), and a personnel table which lists the person's name and department

My Tables and Fields:

Orders	Assigned_to	Personnel
OrderNum_key	OrdersNum_fkey	Personnel_Key
Open_Date	Personnel_fkey	Name
Close_Date		Department#
Static 1

Relationships:

• Orders::OrderNum_Key = Assigned_to::OrdersNum_Fkey
• Assigned_To:Personnel_fkey = Personnel::Personnel_Key

Filter:

• Orders::Close_Date is empty (only want Open Orders)

How do I make a Summary Field or Calculation field that will filter on both, Close_Date is empty and Department# = 1 (or whichever department)?

In MS Access, I would have created a query with these tables and relationships. then filled in the criteria "Close date is null". Then use the "Total" feature and group by Department Number and Count function.  Is something like this possible in Filemaker?

Thanks.

[OlgerDiekstra]

In your Orders table create a numeric field (ie NotClosed) with an auto calc: if( IsEmpty( Close_Date ); 1; 0 )

Then create a summary field that counts NotClosed.

Edited March 8, 2019 by OlgerDiekstra

[comment]

6 hours ago, Scott Pon said:

I'm trying to create a dashboard that shows how many open items per department. 

If you want to show all departments, you should have a table of departments. From the context of such table, each record could count how many of the related orders are still open by =

Count ( Orders::OrderID ) - Count ( Orders::Close_Date ) 

Note that due to the relationship between Orders and Personnel being many-to-many, the total of these counts may not match the actual count of the orders.

 

6 hours ago, Scott Pon said:

n MS Access, I would have created a query

Actually, you can do the same thing in Filemaker too, using the ExecuteSQL() function. But this can get slow if used in a "live" (unstored) calculation field, so if you prefer to go that way, you might script it and present the results on demand.

[Scott Pon]

Thank you for your insight.  I will need to look into the ExecuteSQL function more.  I have not used it. your answers make sense.