Counting Fields!

[FileMakerHarry]

Hello Board!

I have just bought FM12 and upgraded a very small database run on FM6. I have not really used FM before, only to play around with.

I've been asked to write a report to summarise the data but i'm stuck and here to ask for your help.

My table has these fields: TimeStamp, Operator, Product Code, JobNo,

Everyday i want a report to print out automatically onto the bosses desk. So, i have run a search on the entire table for '//' which is todays data. Then, i need to build a report that groups by JobNo and will count the instances of ProductCode in that data set.

A label with this data is made into a table and printed out. We want to know how many are being made everyday.

So, i have the entire days work in a search table. How do I then do the Count? I don't want to list all the records, as there might be 200 all the same ProductCode and Operator. I just want the report to say JobNo = 200.

I've tried so many different ways building the query thing that my head is spinning and i'm lost. Can someone explain it to me or show me where i can find out? I have a summary field but don't think i've put it together right.

Thank you for any help you can give.

H

[doughemi]

Create a Summary field named JobCount. Define it as count of Job Code.

Create a new layout named Report with a subsummary part only (Change the Body part in a blank layout to a Sub-Summary part when sorted by Job Code. Put the JobCount and Job Code fields in it. Make sure the layout is set to view as list.

Create a button named Report and attach a script named Create Report to it:

Enter Find Mode[] //<---Do not pause, do not restore

Set Field[ TimeStamp; get(CurrentDate)]

Perform Find[] //<---do not restore

Go to Layout["Report"]

Sort Records[Restore; no Dialog]//<---Sort by Job Code

[FileMakerHarry]

Doughemi,

Thank you for your input and post!

I have made this report but it doesn't work.

Blank report, a Header which is blank, a footer which is also blank and then the Sub-Summary by JobCode (Trailing) section in the middle.

The two fields in there are JobCode and JobCodeSummary.

I have manually queiried the database using the '//' operator for today and it gave me a result of 213 records.

Switching to the report, in List View, it is blank. In Form View, the first JobCode in the found set comes up with the total number of records (213) rather than being the total number of records found with that JobCode.

I'm not sure what i've done wrong!

[doughemi]

Did you sort the found set by JobCode?

[FileMakerHarry]

Doughemi,

That's perfect. It's just what i need; pretty basic really but everything is there. Now, i've got a printing problem - it only kicks out the Header to the printer.

[FileMakerHarry]

OK, I have another COUNT that i need to make and I tried using the above method of work, but it hasn't really kicked out what i needed.

A user enters a New Record. There is a JobNumber AutoEntered (value from last record). I want a Summary box that shows me all other records with the JobNumber of the current Record.

When using a Summary field to COUNT the JobNumber field, it gives me the total of all the records with a JobNumber. I want to know how many records have the JobNumber of the record I'm currently in.

Does that make sense?

Thank you for any input or guidance you can give.

Harry

[comment]

I want to know how many records have the JobNumber of the record I'm currently in.

If you want to know this immediately (instead of waiting for a scripted report), you will need to create a self-join relationship matching on JobNumber and count the related records.

However, this shouldn't be necessary if you have followed my advice in another thread of yours:

http://fmforums.com/forum/topic/86065-basic-portal-advice/#entry395598

Then you would let each Job record count its children, using the existing relationship.

[FileMakerHarry]

As per the other thread, i now have a window with all the records related to the current record, according to the JobNumber field entered.

Then you would let each Job record count its children, using the existing relationship.

Can you explain this for me further, please? I have a Summary field on the Jobs table which is a running Count of the JobNumber field, but it shows the total of all records, rather than the number of those related.

I can understand what you're saying in your quote, but how do i make FM do that? i can't see where i can put that sort of thing into the Count. Many thanks.

[FileMakerHarry]

Oh, OK. I selected the Summary field in the Layout during edit and repointed it to the same field in the copied table and it's worked.

I get what you mean now about using the copied tables, so thank you yet again. It's appreciated. I'll put that bit in tomorrow and it will really help us.

[comment]

You can count related records using a calculation =

Count ( Related::Matchfield )

or - as you have discovered - place a summary field from the related table on the layout. Note however, that this is for display only.

I get what you mean now about using the copied tables,

Ahm... there is nothing that should be copied here. On the contrary, the entire idea is based on storing data only once.

[FileMakerHarry]

OK, so the other instances of the tables are not entire copies, they are just a graphical representation?

In my relationships diagram I have <Table1> and <Table2> and <Table3>

Thanks, I did think that was strange.

[comment]

If the relationship is a self-join, then it uses 2 occurrences of the same table. A TO is not ""just a graphical representation" of a table - it has its own context - but neither is it a copy of the table.

My point, both here and in the other thread, was that it would be better to use 2 individual tables in a parent-child relationship instead of a self-join.

[FileMakerHarry]

Understood. I have done that now and it is working as you have outlined. Thank you for your patience and help!

Now, i need some help doing automatic imports and searching for repeated fields! I'm not getting the results i think i should, so I'm obviously doing something wrong.