writing my first script in Filemaker Pro 8.5 to create a report

Goal: count records in a found set (3 different reports or one report with 3 sets of data)
Condition One: contacts:touches-category has a numeric value
Condition Two: contacts:agencies is found in agencies:agencies list

Result example
contact [Customer Service,Communications,or Advising]
total [number of records that match the contacts:touches-category]
Enter Browse Mode[]
Go To Layout["nul" (nul)]
Loop
  If[contacts::Contacts to Touches:Customer Service > 0
     and contacts::Contact_x_PeopleBldgAgency;Agency_c = agencies 2::Agency]
    Set Field [nul::total; Count ( contacts::Contacts to Touches:CustomerService)]
    Set Field [nul::contact = "Customer Service";
  End if
End Loop
Halt Script

Open in new window

maryj152Asked:
Who is Participating?
 
billmercerCommented:
Okay, so you're exporting data from one database, which you have little or no control over, to one of your own, so that you can manipulate the data more easily. The problem is, you may not be retrieving all the information you really need to do your calculations, and you may also be unintentionally changing the data during the export process.

To reproduce in your own database the relationships from the original database, you will need to include primary and foreign key fields in your exports, and you'll need to be sure you are exporting only fields from the actual tables you're trying to work with.

If you create an export from the contacts table, but also include values from a related table, then you may end up with unnecessary  duplicate records. The sample data you provided suggests that may be the case.

Frankly, it sounds like the amount of trouble you are going to to produce this report is probably not worth it. It would probably be more cost effective to pay the developer to produce this report for you.



0
 
billmercerCommented:
Can you restate your question in a way that describes more what your desired final result will be?

The script you provided doesn't look like it will do anything useful. It doesn't iterate through a set of records, so it will just repeatedly count the same record over and over. This also means the script will never stop running.

To fix this, you would need to add a Go To Record Next script step inside the loop, which will tell FileMaker to move through the records in the found set. You'll also need an exit condition for the loop. The easiest way to do that is to check the "Exit after last" option on the Go To Record step.

You said "Goal: count records in a found set (3 different reports or one report with 3 sets of data)"
but I'm not clear on what three sets of data would be included. You only mention two criteria for searching on.

You seem to be looking for a count of the number of records that match certain criteria.

Usually the simplest way to do this is by performing a find for the matching records, then simply retrieving the found count with the Get() function.

So you'd have something more like this:
Go To Layout [Nul]
Enter Find Mode
Set Field MyField1; Criterion1
Set Field MyField2; Criterion2
Perform Find
Get(CurrentFoundCount)

The details of how to construct the find criteria just need to be worked out.
Need a more detailed explanation of what you're trying to produce.
0
 
lesouefCommented:
unless I don't get your point of view, you don't need a script to get these figures.
a sub-summary part in the layout should get it. make one based on the right field. but it is very unclear so far, as the field names above are not properly mentionned
and you script won't work, it misses the goto next record step.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
maryj152Author Commented:
The data I am working with comes from another databased/table over which I have no control.
I export the fields that I need to another table to work with them.
The original table Contacts has two fields that are check boxes (Customer service, Communications)
When the data is exported, the fields have a value of 1 or nothing.
The third field (Advising) has a numeric value of minutes spent
There is another field PortalCount that has the number of people affected by each contact category
What I need to do is (usually with a time frame of a month, I can make this filter before exporting the data)
get the total of PortalCount for each of (customer service, communications) and a count of each instance of Advising that has any value.


0
 
maryj152Author Commented:
For got to mention
The only records to be counted are the ones where the Agency field in the Contacts table can be found in the Agency field of the Agency table
0
 
lesouefCommented:
so would have been better to have customer service and communications in the same fields.
Can I get the files? would be much quicker than explaning how to do this...
0
 
Peter HarrisFileMaker Developer at CognitiveCommented:
When doing a loop thru all records you usually:
Find All Records Go to Record (First)
and within the loop have a Go to Record (Next) with exit loop on last turned on
Use Set variable to hold variables as you go (variables usually start with dollar sign eg $counter

As discussed don't bother with a loop if a summary field in a report will do.
0
 
billmercerCommented:
get the total of PortalCount for each of (customer service, communications) and a count of each instance of Advising that has any value.

So as I understand it you need three separate counts, one count of all records with the customer service flag, one count of all records with the Communications flag, and one count of all records that have some value greater than zero in the Advising field.

As you mentioned, you can either create three separate reports, one for each count, or you can use a script to retrieve the numbers and put them into a report format.
The scripting method is probably a bit faster. I already provided an example of the kind of script you'd need. The details of creating the find requests are all that needs to be fleshed out.

To find all records for a date range where the customer service flag is set, you just go into find mode, insert the date range into the date field, and the number 1 into the customer service field, then perform the find. Use the Get(CurrentFoundCount) function to retrieve the total number of records. That can be stored in a global field or script variable.

Next, repeat the same process, just putting a 1 in the Communications flag instead of the Customer Service flag, and retrieve your second record count.

Third, repeat the process again, but this time putting >0 in the Advising field to find records where some advising was done.

"The only records to be counted are the ones where the Agency field in the Contacts table can be found in the Agency field of the Agency table"

This just means that you have to search for records where there is some value greater than null in the related field. You can put >"" into the related field for the agency in each of the finds I mentioned above to accomplish this.

You can combine all three of these into a single script, and use string concatenation to append your numbers to a single global field that you print in order to produce a report.





0
 
maryj152Author Commented:
Still no results but I fell I am getting closer, as I said this is my first attempt at scripting. I tried to make a summary report but could not find a way to validate that the Agencies listed in Contacts table were in the Agencies table. I only count records that fit this requirement.
I am attaching images of the result I would like, the last script I tried and the two tables that I am working with.
nul.jpg
script.jpg
agencies.txt
contacts.txt
0
 
billmercerCommented:
"I tried to make a summary report but could not find a way to validate that the Agencies listed in Contacts table were in the Agencies table"

A summary report can provide counts and totals, but it does so based on whatever the current found set may be. In your case, what you need is two things: First, you need to perform a find to get the set of records you are interested in, THEN you can run a report based on that found set.

Usually the easiest way to script a find is to first perform the find manually, so you can test your criteria. Go into Find mode, and enter criteria as I mentioned previously, then see what you get.
To find records that have a value in Agencies that matches the Agencies table, you create a relationship between the two tables based on that field, place a related field from that table onto your layout, then search for records that have a value in that field. Any records that do not match any of the agencies records will be blank in the related field.


The script you provided is iterating through a set of records and counting occurrences of certain values. That approach can also work, but it's generally not the most efficient way, especially if you have a large number of records. Usually it's more efficient to do a Find or a Go To Related Records to get a found set, then count the total number of records found.

If you'd like to use the looping script to accomplish your task, you can do so, but you'll still need to create a relationship between the contacts and agencies so that you can test to see if a related value shows up in that field. If it does, the record is a match, if not, then it's not a match.

0
 
billmercerCommented:
The export files look like they are from a contact management package such as GoldMine or Act. I noticed that there appear to be a lot of duplicate records. Does each record represent an event like a phone call or visit? Or is each record supposed to be a customer?
0
 
maryj152Author Commented:
To: billmercer

As I stated this is my first attempt at  a script and some is making sense.
I found http://www.filemaker.com/help/html/create_script.12.2.html
It helps to explain formats and options.
I won't have time to work on it again until Monday and then I will  try your latest suggestion.
0
 
maryj152Author Commented:
each entry is a separate contact.
0
 
maryj152Author Commented:
The following script gets me the count I need depending on the category of the contact.
(there is another script for different category)
Now I need to separate members from non members.
Members Agencies are in the Agency table.

Is there an sql "IN" equivalent in filemaker or do I need to establish some sort of relationship between the contacts table and the agencies table?

Perform Find [Restore]
If [Get (LastError) > 0 ]
    Show Custom  Dialog [no records;  No records. OK to modify. Cancel to Browse.]
    If [Get (LastMessageChoice) = 1]
      Modify Last Find
    Else
      Enter Browse Mode []
    End If
End If
Go to Layout[contactsCS (contacts)
Extend Found Set [Restore]
Set Error Capture [On]
0
 
billmercerCommented:
FileMaker doesn't use SQL, everything is done via layouts and relationships.

If you've got a found set of records and you want to narrow that set down to a subset, then you can use the Constrain Found Set option to find only those records that have a non-blank value for the agency. If there's not currently any sort of relationship between the contacts and the agencies then you'll need to create one.

The simplest way for a person to be related to an agency is to put an agency ID with each person. However it's often better to create a third table of linking records. That gives you the flexibility to have multiple contacts and multiple agencies related to each other in more complex ways. For example, one person might have several agencies they are associated with, and one agency may be associated with several people.

0
 
maryj152Author Commented:
billmercer

When I tried to Constrain Found Set, I could select the first field then choose ==, but that was it. I couldn't figure how to choose a field to match. A non-blank field won't work. I need to count members' and then non-members' contacts.

Previously you wrote

To find records that have a value in Agencies that matches the Agencies table, you create a relationship between the two tables based on that field, place a related field from that table onto your layout, then search for records that have a value in that field. Any records that do not match any of the agencies records will be blank in the related field.

The contacts table contains all contacts and the agency of the person contacted.
The agency  table contains only the names of member agencies.

How do I create this relationship between contacts::agencies and agencies::agencies?
Do I need another table?
0
 
billmercerCommented:
"When I tried to Constrain Found Set, I could select the first field then choose ==, but that was it. I couldn't figure how to choose a field to match. A non-blank field won't work. I need to count members' and then non-members' contacts."

This might be simpler than you think. What is a non-member? Is that a person that has no value in the Agency field? If so, then all you have to do is do one search where you look for contact records that are blank in that field, then repeat your search, but this time OMIT records that are blank in that field. This is done by checking the "omit" box when doing the search or constrain.

The fields that you want to look for all need to be on the layout you are using when doing your find.


"Do I need another table?"
Well, maybe...

Currently you have a table of contacts, and in that table is a field that identifies the agency for that person, and you have another table that contains the agencies.

This means each agency could be connected to multiple people, but each person is only connected to one agency; this is called a One-To-Many relationship. If you know for sure that this will always be true, then you don't need a third table.

To create a relationship between two tables, you click on the foreign key field in one table and drag it to the primary key field of the other table. In your case, the foreign key would be the agency field in the contacts table, and the primary key would be the agency field in the agencies table.

If one person could have multiple agencies, then you'd have a many-to-many relationship, and you'd need to create a third table of linking records between the two main tables. This does require more work to set up initially, but gives you more flexibility to change the system and add new features later on.

It sounds to me like you may have a less than perfect database design, and this could be making things more confusing for you. You might want to read up on relational database design.
0
 
maryj152Author Commented:
Unfortunately I have no control over design, layouts or queries.
This is outsourced. We have a contact person who communicates with the contractors.
On the old database we were able to create complex finds by going from one table/layout to another and using Add Request.
With the new system we can only add request to the layout we are in.
If I need information for any reports I need to create I have to request that a layout be created.
I submitted the information I needed for the particular one that I am trying to do on my own last September. I only recently was given permission to view in Layout mode so I could see the names of the fields being used. Before that I would export more fields than I needed and then find the one with the correct data. The field names are anything but obvious to someone who had nothing to do with creating them.

The way I have been creating reports is exporting relevant data to my desktop and then manipulating the data as I need.
I have created some Summary Reports that do what I need for other reports, but because of the members' vs non-members' counts a summary report won't work. What I have been doing with this is manually omitting, sorting and counting. That is why I would like a script.

Next week is the first of the month.........
0
 
maryj152Author Commented:
Thanks for the help
It took from September til January to get the contractor to add a check box for two categories that need to be counted separately. I have requested that a checkbox, radio button or field to indicate membership be added to the contacts layout. Once that is done it will be easy. I guess I will just have to wait till then
0
 
billmercerCommented:
From your description, this report does not seem like it should be either difficult or time consuming to produce. If you're not satisfied with the quality of support you are getting from the developer, you

You said this work is being done by a contractor: you might want to check the contract and see what the terms are regarding work like this.

There's another possible way you could handle this, depending on your licensing. FileMaker Pro does have an ODBC driver which allows you to query a FileMaker database using SQL syntax. That might allow you to create a report in a platform like Access.

FileMaker doesn't use SQL at all, so the ODBC driver is really more of a SQL emulator, and performance is not great, but it might be an option for you.
0
 
maryj152Author Commented:
I do not communicate with the contractor. Our "database person" does and all request have to go through her.

I am almost finished with this month stats, with a little help from a couple of new scripts that work.
Maybe I will try to set up something for next month using sql. I am more familiar with it. I think I can create a nested loop in sql that should do what I need.
0
 
billmercerCommented:
Just so you're aware, the ODBC driver for FileMaker provides only rudimentary SQL functionality. You can do select and update queries, but you won't have an actual SQL language available. You won't be able to create stored procedures, or do sophisticated SQL programming. Also, FileMaker's data types are different than SQL data types, and this can cause confusion.
0
 
maryj152Author Commented:
I am not planning on trying sql insinde filemaker.
I am going to export the data I need to my desktop and see what I can do in OpenOffice since I don't have Access
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.