1 2 Previous Next 15 Replies Latest reply on Nov 3, 2010 11:23 AM by philmodjunk

# Calculate Total Number of specific assets in Asset management Starter Solution Template

### Title

Calculate Total Number of specific assets in Asset management Starter Solution Template

### Post

I'm using the Asset Management Starter Solution customising to make a database of equipment owned and operated by a production company I've added a few fields to the design including 'Kit', 'Quantity' and 'Total quantity of selected asset'.

Kit refers to the different bags used to carry all the accompanying pieces of equipment required for the operation of assets like cameras. As a result my database has a record each for the different models of Memory Cards used by the cameras and a separate record for each memory card of each model found in each specific kit. This is probably quite an inefficient way of going about things and I'm open to suggestions on how to improve it, I'm using the trial version and this database as an initial means of teaching myself how to build a functioning database for other purposes.

This aside though, I'm trying to fill the 'Total of selected asset' field with the sum total of certain specifically selected assets, in this case memory cards, so, while separate records exist for all memory card types and all cards in different bags I'd like to be able to provide the information "How many memory cards do we have right now?". I've gotten as far as defining the the 'Total...' field as summary of the quantity field but of course this simply adds up the total number of all items in the database not the just the memory cards. I'd like to know how to make it for only the memory cards and how generally to make it possible for a user to find out this information for any given set of records with certain fields in common, eg. Kit, Category, Model etcetera.

• ###### 1. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

Do you have a field that records a value that identifies each type of item you want to "count"? (looks like Category would do that for you).

If you have such a field, you can sort your records by the value in this field to group them by type. Then a summary report with your summary field in a sub summary part can show the subtotals for each type of item.

Here's a link to a tutorial on setting up a summary report that may help to get you started: Creating Filemaker Pro summary reports--Tutorial

• ###### 2. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

The Category field is exactly what I'm using as an identifier, I'll give that a shot, I'm reading through the tutorial now. Thanks a lot again PhilModJunk

• ###### 3. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

Hi again, Phil or anyone else whose reading, looking through the tutorial I can see it probably is what I'm looking for but despite reading it over and over I'm fairly confused how to apply it to my database. Mine is a single table database at present, I tried to define  relationship between the 'Quantity' and the 'Total quantity of selected asset' field but the 'Total quantity of selected asset' field is greyed out. I changed the field to a number type rather then summary and was able to make a self joining relationship between the two fields so that I now have two identical tables with a single relationship. I was then able also to change the 'Total....' field back to a summary of 'quantity'. Unfortunately I'm really quite confused where to go from here.

I didn't understand the instruction

"Define a relationship:

Invoices::InvoiceNumber = LineItems::InvoiceNumber"

I don't know what the notation means, I guess the double colon is just a relationship but not quite sure what the equals is about. I find the part setup option only I'm not completely sure what this actually does, on the side of the layout there were two tabs saying 'sub-summary by category (leading)' (I defined to it to sort by category hoping I could get the 'total....' to be a total of all items of a certain category) and another tab saying 'body'. I thought I'd missed a step when I saw the stuff about 'print above/below' options so I tried to delete these tabs and start over but it deleted everything in the 'sub summary' section so I had to undo this and now I'm stuck with it. I started creating a new part so I could get the menu back to see where I'd gone wrong and I notice that there are no options to do with printing above or below or anywhere, is this because the tutorial is for Filemaker 10? Anyway I cancelled this second part.

I dragged the 'total...', 'Quantity' and 'Category' Fields into the Sub summary part but it still doesn't seem to do anything. Also I've somehow accidentally set the part setup to now display the border between body and sub summary horizontally in my efforts to get rid of them and now I don't know how to set it back to vertical which would be preferred. I exited layout mode, set layout to list view and view as continuous list and said sort by category, a list is generated of all the the records and it is correctly sorted by category but the fields for 'Total quantity of selected asset', 'quantity', 'kit' and a bunch of other fields are not displayed. Looking in form view reveals that the 'Total...' field is still returning '28' which is the total number of records in the database. I understand the logic behind why this is happening, that if I set this field to return the total of the quantity field then this would be the result but I don't properly understand the steps taken in the tutorial to know how to make this category-specific or how to make it show up in the list view.

I'm completely new to filemaker and I'm so far really confused by it.

• ###### 4. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

With AssetManager's single table, you can just skip the instructions about setting up a relationship. That's solely to enable you to pull data from a related table into your summary report. In your case, all the data is in one table, so you don't need to do that.

The key detail is to specify a "sorted by" field for your sub summary part such as your Category field so that when you sort your records by this key field, the records are grouped by category and you see a sub summary part with any summary fields and other fields in it to form a sub header and/or a sub total.

In your case, put the Category field in the sub summary part along with your summary field and any layout text you want, then sort your database by the category field. Once you get the basics working, you can play with some of the options--such as creating two sub summary parts, one above for a sub header and one below for a sub total-- to enhance the basic report.

• ###### 5. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

Hi thanks a lot. I started a new database from scratch rather than trying to modify the template and figured out what I think is the same as what you just said it was so much easier just starting out from zero. I think in my efforts to make life easier for myself I ended up making it more complicated.

Problem now is, if I look at the database as form view the total is incrementally updated as you scroll through the records so once you get to the last record in a sorted group you will get the total 'quantity' of items in that group as I wanted but it means having to flip through every record in this sorted group first and also for records between the first and last it'll be confusing for users to understand what the 'total' field they're looking at is a total of as it mightn't occur to them that it's incrementally adding all of the 'quantity' in the 'solid state media' category and might think it is the grand total of that category which is actually what I want it to be.

Looking in list view somewhat fixes the problem as it lists the total for each of then 7 categories I've defined however it for some reason shows only the 'total' field and nothing else leaving me with 7 instances of 'total' written on the screen and the corresponding number but no indication what it's a total of because none of the other fields are visible.

• ###### 6. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

How have you set up your layout? Place close attention to any fields that may have one or more pixels on or above the boundary line the next higher part. To avoid unexpected results, use your arrow keys while in layout mode to "nudge" the fields down until you are sure that no part of it touches or crosses the line.

Beyond that, I'd have to know more about what you put in each layout part, what settings you have specified in the sub-summary part and how you've sorted the records.

• ###### 7. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

Excellent, the form view problem is still there but I've got the list view working how I want, I completely forgot about aligning the fields into the various part boundaries, now the layout is a bit odd with the 7 'total's lined up on screen and at the very bottom all the other information but I'm sure if I tweak around with the different parts and sections and everything I can get that working better. Thanks a lot Phil, I think I'm on my way to understanding the program a bit better.

• ###### 8. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

Sounds like you have fields in the header that should be in the body if all your subtotals are appearing in a list at the bottom without any data in between them.

• ###### 9. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

Hmm actually everything was in the footer, I accidentally put it there thinking that I was putting it in the body section, that's fixed now currently the total which now in the from scratch database is called 'subcategory total' is not shown in list view but actually this isn't really that big a problem for the moment at least it shows up in form view there is this problem with the total being incremental though. I don't know what to do here, I just want each record to include a field which displays the total number of items in that record's subcategory, so in this case as I've only entered some 10 pieces of data in to the whole database the only records sharing sub categories are 'solid state media' in which there are 7 so ideally I should see 'subcategory total: 7' on every record in with 'subcategory' field filled with 'solid state media'

• ###### 10. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

It occurs to me the easiest thing to do would be to setup a table of Totals for each of the fields Category, SubCategory, Model, Kit, Quantity with corresponding fields 'Total of Category' 'Total of Subcategory' etc. but also with a field for each entry in the value list of the drop down fields and of the previously entered data of the text fields. I could then use that table to generate reports with summary totals however I've tried to do this and when I specify each of these totals fields as a summary I don't have the option to make them a total of any fields from the first table, even if I initially link each of the totals fields to one of the fields in the first table in relationships management for example I've started with just the 'category' field to try and keep the table initially small and easy to work with, so I have 7 fields in my totals table corresponding to the 7 entries in the value list and these are all individually linked to the 'category' field in my first table.

It seems I can only choose fields for my summaries that are within the same table as one another. How can I create a table of summaries containing the total number of entries in each of the 5 fields mentioned in my first table?

• ###### 11. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

Much depends on the needs of your report. The subtotals work using a simple summary field when it is placed in a sub summary part that specifies your category field as the sort field and when you then sort the records in your found set by this category same field. Also, the summary field only counts records in your found set so performing a find or other action that changes which records are present in the found set will change the total computed by a summary field. This method will work in List View. The Sub Summary part will not be visible in form view so this will not work if you need to see category subtotals in form view.

You do not need an extra table in order to compute category totals though that method could be made to work.

Here's a calcualtion field based method that will give you your category totals without needing the extra table and which will report the total number of assets in each category regardless of which records are in the found set:

In Manage | Database | Relationships, click on your Assets table to select it. Then click on the button with two green plus signs at bottom left to make an additional table occurrence of your Assets table. Define a relationship between the two by dragging from the Category field in one box to the category field in the other. This gives you this relationship:

Assets::Category = Assets 2::Category    (You can rename assets 2 to a more  meaningful name if you want.)

Now you can define a calculation field as Count ( Assets 2::Category ) and this field will return your category totals in form view.

• ###### 12. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

Thanks, that's working well, it's brought up two problems for me though. The first is that after the success of this I defined relationships for lots of other fields in the table with 2nd occurrence of the table and began setting up totals fields for each of them with calculations to give me a count. For some reason they displayed no data and also seem to have stopped the total of category field from showing anything either when it had previously been working. Deleting these relationships restores the total of category field to working order and I've successfully managed to make a field for total of subcategory so I suppose that if I keep defining relationships and setting up fields individually it should work but I don't see why it makes any difference if I try to do them wholesale.

The second problem and more important is that, I forgot to mention I'm using these totals so that a user can immediately see a total number of items within a certain criteria that is category, subcategory, model and kit however I'm currently seeing a total of records in each of these criteria but unfortunately my database can have one record with many items as described by the quantity field. I'll need to find a way to link the totals to the quantity field so that, if there are for example, 8 items of the sub category: Memory cards but only 3 8GB model cards and 5 16GB model cards the total of subcategory field should read 8 (the total number of memory cards) but at the moment it would read 2 for the 2 records entered matching that category and the total of model field should read 3 or 5 depending upon which model you're looking at but at the moment would read 2 for the 2 records of models, 8GB and 16GB.

Any ideas?

• ###### 13. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

This would be much easier in a summary report where you can group items by category and sub category with the same summary fields computing counts or totals in different sub summary parts.

To get the total of the qty field for a related group of records, you can use Sum() instead of count.

This next part answers both your first and second paragraphs as they touch on the same issue.

For each such relationship where the fields you use for matching to related records change, you have to create a separate table occurrence. If you just drag between more pairs of fields you are adding additional criteria to the same relationship where all such fields in one table occurrence have to match values in all such fields in the other table occurrence. You can see this more clearly if you double click the line linking your TO's where you'll see something like this:

Table1::Field1 = Table2::field1 AND
Table1::Field2 = Table2::Field2 AND
Table1::Field3 = Table2::Field3

Thus, you'll need a central table occurrence you'll base your layout on and which will specify the "context of" table occurrence for your calculation fields where you use sum or count to get the totals and subtotals you need.

If you need to counts and/or totals of all assets of the same category you need one related TO for matching by the category field. If you need counts/totals for assets in a sub category such as model, you'll need a third TO that matches either just by the sub category field or possibly the category field as well. (Depends on whether the sub category field might have the same values for fields in two different categories.)

• ###### 14. Re: Calculate Total Number of specific assets in Asset management Starter Solution Template

I'm sorry but I just can't seem to wrap my head around that. I get that I must make separate Table Occurrences for each of the relationships now, but I'm not quite sure how to use the sum() function. Do you think you could give me an example what that might look like.

I'll just recap the structure of things and the nature of what I'm trying to achieve I have a database of equipment used at a production company. Each record has the fields Category, Subcategory, Model, Kit and quantity. (There's others but these are the important ones). The best example I can draw on for talking about  achieving specific aims is the category of storage and the subcategory of Solid State Media and further in to the different volume sizes of the different models of Solid State Storage. I would like a field which will tell me the total quantity of a given entry for a given field: So I would like for example to see the total quantity of storage devices and another field for the total quantity of solid state media.

As I couldn't really grasp how to use the sum function I had a go at a total of function using data only from within the one, original table, I had this field set to display the total of quantity when sorted by category. It was my understanding that this would result in the value of the field quantity for each record containing the same value in the category field to be added up thus giving the total number of items of a particular category/subcategory/Model/Kit and restarted to zero and added up once more for each entry in those fields. The result of this was not at all as I'd expected and instead produced a strange figure which increased by one as I scrolled through the records.

You probably already understood this but I'm afraid I can't seem to figure out how to apply the advice in the last post, could you explain it to me again?

1 2 Previous Next