Running Totals

[mdb28]

Hey guys, I am really really new to File Maker. I have been asked to build a date base and I need to track the number of projects according to their status. So each time a file is created it is assigned a status and long the bottom in the footer we need a line that says "53 Files Initiated, 16 Scheduled ....."

I have no idea how to do this, can someone please help me?

Thanks

- Matt

[LaRetta]

Welcome to FM Forums!!

I am hoping you mean records and not files or tables. Also, how many different Status' do you have? Do you want this line to display always along the bottom of a form layout or only for reports or ?

[mdb28]

Yes, records. There are 6 status that could be assigned.

in footer we need something like

6 Initiated | 4 Scheduled | 15 Installed | 0 Waiting | 2 Cancelled | 1 Removed

Edited October 18, 2010 by Guest

[Jeep Watson]

I've attached a sample file showing one method using a separate table occurence for each of the statuses (I only implemented 3) and establishing relationships based on those statuses. It might be a bit to get your head around initially but read the Filemaker help files about relationships.

StatusTrack.zip

[LaRetta]

Another option would be to use one self join and a calculation of:

Let ( [

l = ValueListItems ( Get ( FileName ) ; "Status" ) ;

v = List ( Status 2::Status ) ;

$i = $i + 1

] ;

ValueCount ( FilterValues ( v ; GetValue ( l ; $i ) ) )

& " " & GetValue ( l ; $i ) &

Case ( $i < ValueCount ( l ) ; " | " & display ; Let ( $i = "" ; "" )

)

)

File attached. Result is exactly as you specified. However, I am unsure of the consequence if the record sets are huge.

NOTE: When creating a calculation which refers to itself (and you can't use the Self function), create the calculation but leave the calc box empty and save it. Then create this calc within it because it must refer to itself ('display' is the calc name). There may be ways around that but I was in a hurry.

SECOND NOTE: If you were using vs. 11, you could most surely use the calc within layout-level variable with conditional format but I don't have time to try that.

Status.zip

Edited October 23, 2010 by Guest
Added two notes

[mdb28]

Thanks for the help, I'll give it a try let you know how it turns out.

_____________

I got it to work! Thanks so much!

Edited October 25, 2010 by Guest