Then your calculation field VendorFlag = If (Vendor="Microsoft";1)
Summary Field Count (VendorFlag)
You should describe the context behind what you are trying to do.
Simply sorting your records by vendor and using the "restart totals when grouped by vendor" would produce the running totals, but I suspect that there are some additional details that will keep that very simple approach from working for you. I suggest describing the layout design where you want to see this running total.
Let me give you guys a better picture behind what I am trying to do in order to get my running totals correct. Here is the screen shot of how my layout looks currently. I have circled where the name of the vendor is, so I want different counters depending on the vendor. So Again lets say in this example its Zebra Technologies, everytime there is a record present in this database with that name I want it to add the total and give me the final dollar amount I have spent on that company.
Hope this clears it up a little please let me know if you have any further information.
Technically that's not a running total, but it is one you can easily get with a self join--though I suggest something more rigorous that matching a name field to do it.
Let's assume that you have these two tables and this relationship involved:
PurchaseOrders::__pkPOID = PurchaseOrderLineItems::_fkPOID
and Total Cost is defined as: Sum ( PurchaseOrderLineItems::ItemCost )
Add a new table occurrence of PurchaseOrders, name it PurchaseOrdersSameVendor and then you can link PurchaseOrders to purchase orders by vendor:
PurchaseOrderes::_fkVendorID = PurchaseOrdersSameVendor::_fkVendorID
Then Sum ( PurchaseOrdersSameVendor::TotalCost )
will produce the total spending for the current vendor.
I'd add a table for vendors with a relationship like this:
Vendors::__pkVendorID = PurchaseOrders::_fkVendorID
If the notation I am using is not familiar to you, see this thread: Common Forum Relationship and Field Notations Explained
Thanks for the quick response, I tried the what Marc had posted "VendorFlag = If (Vendor="Microsoft";1)
Summary Field Count (VendorFlag)" and it worked, except for I changed the VendorFlag=If(Vendor="Microsoft";Total Cost). I looked at what you had responded to with the above post but it looks a little too complex for me for now.
With the current way I did it, can I also narrow down which dates it takes the total cost from. Lets say I want to take "Microsoft" and only show the total cost from 01/01/12 to 01/06/12? Or how can a user input the dates as a field and have the calculation done automatically?
Thanks for your time!
If I understand what you have there, won't you need a separate calculation field for every vendor? And how does the "count" return a total?
There are several ways to use the self join relationship to limit the totals by date or a range of dates. Are you asking for help with the calculation field march suggested or the relationship based method I am suggesting?
Using variations of my method, you can include a pair of date fields in the relationship, or you can use a filtered portal that filters by date range with a summary field inside the portal.
And if you have FileMaker 12, there are ways to use ExecuteSQL to produce the desired total--without adding any additional occurrence boxes to your relationship graph.
I ended up creating a new layout called running total and I used the formula I posted above for a running total. I changed the If statement to If(Vendor!=1;Total Cost). So ofcourse vender will never equal 1 and therefore it will always compute this formula.
Now as far as the sorting goes, I used the built in sort feature that lets you search by vendor and specify dates. So if I wanted to show Microsoft from 01/01/2012 to(...) 06/01/2012 I just type that in with the ... and it works.
Once again thanks for the help!