Jump to content
Salesforce and other SMB Solutions are coming soon. ×

build a report counting text fields


calliex
 Share

Recommended Posts

I know how to do what I want in sql but not in filemaker.

 

I have a database with 800 records. There is a field called Rig, this field can holds the name of various Ambulance services. I want to produce a report that will list the numbers of records for each service and the total number of ambulance calls which should be the total number of records.

 

In SQL I could say something like Select count(Rig) from table where Rig = "Pgh");

 

I have played around in filemaker and can not tell how to accomplish this.

 

Any help would be appreciated.

 

Joe

Link to comment
Share on other sites

For each individual record, does "Rig" hold a single value?

 

Create a calc field, numerical, formula =1, call it "One"

Create a summary field, defined as the Total of One, call it SumOne.

 

Make a layout. Select List View /Report and go through the wizard. When it asks what fields to include on the report, just select Rig. Indicate that you do want to show totals, that you do want to total by category, that your category is Rig, and that your summary field of choice is SumOne. Say yes to creating a script.

 

When you're finished with the wizard, you can delete the report body entirely, the only part you need is the subsummary section which should display Rig and SumOne. When the script is run, it should sort your data by Rig and display this layout in Preview Mode.

 

You should get a list of categories (each existing value of Rig, listed once) and the totals in each of those categories.

 

Grand total at the bottom if you ask for it during the wizard.

 

(Hey, we used to have to construct all this by hand!)

Link to comment
Share on other sites

If, on the other hand, some (or all) records have multiple values for Rig, you get to do some constructing — the built-in subsummary-report feature doesn't really handle that. Let me know if such is the case, and I'll give you the instrux for that.

 

The short version is that you create a special relationship that snags totals within each category, and via that relationship sums up the One values, then changes the value to the next value of Rig until all Rig values have been addressed, and "write" the results to a global field along with the Rig category, and the final value of the global field becomes your report. (Not the only way to go about it, but a viable one)

Link to comment
Share on other sites

Hi, calliex.

There is many ways to do it.

1. You can do it by using getsummary() function.

2. You can do it using Subsummary part in the report layout.

3. You can do it using self relation

4. You can do it using global field in relation

5. You can do it using export with groupping by Rig, this is like SQL.

 

I attached example for the first way. In example you must sort records by Rig, and you will automaticaly see your goal in Count_Rig field.

Link to comment
Share on other sites

OK First each record will have a unique value for rig which happens to a Ambulance agency(Usually the name of a town).

 

1. What is formula =1.

2. For the report what should be in the layout Rig, One and SumOne?

3. There are others steps in the report wizard that you did not mention should I just take the defaults?

4. I do not understand exactly what is happening with the steps. I would like to understand the logic behind the steps. Can you expand that.

 

I have been looking for a book or internet resource for buidling reports/scripts in Filemaker but have not had much luck.

 

Joe

Link to comment
Share on other sites

aaa

 

The site would not allow me to get your example. I appreciate the help though. The ways you suggest look interesting. I am going to look into them using the help.

 

 

Joe

Link to comment
Share on other sites

AHunter3

 

I know what formula =1 is now. I made the field wrong I did not know there was a type calculation, I made it numeric.

 

Ok here is what happening. I should get five amb servicec. I am getting one.

Can I use the summary field over and over.

The wizard asks for one for subototals I choose SumOne and then click on subtotals. The next screen ask for a summary field for Grand total. I used the same one SumOne. When I am done I get the following.

 

Amb Serv 1

 

Amb Sev 2 173

819

 

I do not get the other three.

 

If I play with the record scroll bar. I will get the others depending on where I move the record pointer.

 

Joe

Link to comment
Share on other sites

I'm not sure why Hunter likes that One calc (maybe he's a big U2 groupie? So Cool!). Anyway, I don't think =1 needed here. I'd do it a little different:

Define a field in your table:

Count of Records (summary) = Count of RecordID

 

This summary field is used to count the records, but it only counts records with a value in the field it's counting. If your table doesn't already have some sort of primary key (an auto-entered serial number), then you should add one. This primary ID is also important for uniquely identifying the records in this table for relationships to other table occurences.

 

Next define that new layout as a "columnar list/report". Although the wizard will guide you through picking fields for this, the important thing is having the right fields on the right parts in the final layout. I usually do these manually (if you can figure out that silly wizard, be my guest). The key thing here is to have a Sub-summary by Rig part with fields 'Rig' and 'Count of RecordID'. If you get stuck on defining layout parts, check the PDF manual, as I think there's a decent description in there.

 

If you want the report to show a line for each Rig record, maybe with some details about it, then keep the Body part on the layout. If you just want to see the counts for each Rig, then remove the Body part.

 

What the sub-summary part does is show the count for each value of Rig. BUT sub-summary parts only work when the records are sorted by the sub-summary part's chosen break field, AND they only show up in Preview Mode or when printed.

 

It helps to define a script to do the Sort and Preview or Print for you. I also like to put in a Find to make the report complete. This is what the script might look like:

Go to Layout [ Find Records ]

Enter Find Mode []

Pause Script [ Indefinately ]

#Wait for user to enter Find criteria

Perform Find []

Go to Layout [ Rig Report ]

Sort Records [ by Rig ]

Enter Preview Mode [ Pause ]

#Or put a Print[] script step here for a printout.

Go to Layout [ original layout ]

Enter Browse Mode []

Link to comment
Share on other sites

I have a field I can use as the primary key. I looked up primary key in help and there does not seem to be a way to set it the database. The summary field concept is foregin to me. So the Count of RecordID would be Count of my primary key? Could I count the Rig field?

 

 

Joe

Link to comment
Share on other sites

As I said, the summary Count field will only count records that have a value in the field being counted. This is why it's best to count the primary key (RecordID).

 

In FileMaker, you don't define a primary key explicitly. It is implied that the field with the auto-enter serial number is the primary key.

Link to comment
Share on other sites

Thanks for the reply. I am going have to find a good resource for Filemaker. I bought the missing manual but it does not have any examples in it.

 

I am getting the same result using your method. I get one ambulance service listed with a subtotal and no grand total nor am I getting the rest of the amb services with their counts.

 

I know how hard it is to help someone who dosen't completly understand what you are trying to convey. I really appreciate everyones help. What is frustrating is I know how to do it in SQL.

 

Joe

Link to comment
Share on other sites

If you want a grand total, add a Trailing Grand Summary part, and stick the Count of Records field on it.

 

Your trouble seeing multiple Rigs could be caused by the layout having the wrong View option. When in Browse Mode, check the View->View As List option is selected. It could also be caused by the found set only containing one category of Rigs.

Link to comment
Share on other sites

The view as list shows nothing but a blue screen. I think you are right about the found set. I appreciate the help. I know I am close to getting what I want.

 

Joe

Link to comment
Share on other sites

Ok I got it to work. I saved the report as a script and examined each line.

for some reason the find that was in the report was looking for a value I did not understand. I reset the find to look for all the Rig values. After runnng the script I got the result I expected.

 

Thanks for everyone's help on this. I learned alot and appreciate all your efforts.

 

Joe

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use