solutions wanted I've googled till my fingers hurt

[jhogan1]

Seriouslly, I have been fighting this for some time and I know it can be done but I keep stumbling.

I have imported a spreadsheet of about 250 records into Filemaker 8.5 Advanced.

I prettied up the layout and have everything sorting they way I like.

However, what I want to do is this:

I have total 250 records. I have about fifty fields. I want to find out how many Hispanic students meet "X" criteria (based on one of the corresponding fields).

I get that results quickly and easily. However, when it tells me that its found 30 records -- what I would like it to tell me is the percentage of finds based on my search criteria (meaning what percentage of 250 do the 30 records represent).

It would be nice if I could even go a bit further and search determine the percentage of Hispanic students that met "X" criteria.

SO, yes, that's what I would like. A percentage of the whole population -- and a percentage of the particular demographic.

Since my search criteria will change based on what I am looking for I need something that will produce those percentage results regardless of the search.

Edited May 1, 2007 by Guest

[David McQueen]

This is a cheap trick.

1. Set up a layout that is a form view. It really does not matter how many fields it has, one is probably fine.

2. Script your find such that the find is done, the script goes to that layout, enters preview mode, goes to record request last and then sets a global field with the current page number (which is why form view is important).

3. Have a calcualted field which is the total record count.

4. Use the global and the calculated field to give a percentsage of records found.

HTH

Dave McQueen

[jhogan1]

thanks for the speedy reply David. I am sure that will work.

But, perhaps I should have identified myself more on the 'novice' side as I'm unable to follow those directions.

I created the new layout -- that part was easy enough. But, then it wanted me to create a field based on the data from my first layout.

I've honestly never worked with scripting before.

[David McQueen]

1. go into layout mode

2. go Layouts -> New Layout/report

3. At the top it shows which table occurrance you are in - Make sure it is the correct one.

4. Give the layout a title

5. Pick Standard Form and click next

6. this is where you select fields for that layout. Select one field and then click move and then click next.

7. You are now in the layout themes screen. Just choose default. You really do not care what this looks like. Then click Finish.

8. go to Layouts -> Layouts Setup -> Views. Make sure that only form view is selected.

You now have a layout that you can use for page counting.

9. Now you need to define some fields:

g_PageNumber -> Number field with storage type Global

c_TotalRecords ->Calculation returning a number. The calculation is under the Get functions and is

Get ( TotalRecordCount )

Make this an unstored calculation so that as you add recrods, it will update itself.

c_PercentHits = g_PageNumber/c_TotalRecords

Again an unstored number calcualtion.

10. Now you need to set up an automated search.

Click on Scripts ->ScriptMaker -> New Script. this brings up the Scriptmaker dialogue box.

Give your script a title

I am assuming that you are on the layout where you do your searches.

Show/Hide Status Area -> Select show

Adjust Window -> Resize to Fit

Enter Find Mode -> Select Pause, do not specify find requests.

At this point, you have entered find mode and can enter criteria. The script will stay paused indefinitely. At the sdie fo the screen, the status area will have two buttons, continue and Cancel. continue will contiue the script and cancel will abort both the script and the find.

Once they have done their find, they will click continue an dthe balance of the script below will execute.

Go to Layout (Your layout that you defined)

Enter Preview Mode

Go to Record/Request/Page -> select Last

Set Field (g_PageNumber)= Get ( PageNumber)

Go to Layout (Original Layout)

Toggle Status Area (close)

Enter Browse Mode

This will have brought you back to your original layout with your found set and the field c_PercentHits should now contain a value. If you use the number format to show a percent you should have what you need.

HTH

Dave McQueen

[jhogan1]

thanks dave,

If I can't make that work I have no business working on a computer. -)

[David McQueen]

LOL

The goal of a forum such as this is to empower people.

Sometimes you start too high up and other times too low down in your explanations. It doesn't really matter if in the end the recipient of the post is better off than they were before.

Even better if they go away with that "Warm fuzzy Feeling". ;-)

[Genx]

The goal of a forum such as this is to empower people.

True that!

[comment]

I believe there is a much simpler method to get the number of found records, and that is the Get ( FoundCount ) function.

If you define an unstored calculation field (result is Number) =

Get ( FoundCount ) / Get ( TotalRecordCount )

and format this field to diplay as percentages, you should be done.

The second request is a bit more tricky. You can get the total of Hispanic students - whether found or not - by setting up a self-join relationship, matching on the relevant category field. A calculation of

Get ( FoundCount ) / Count ( Related::CategoryField )

should get you percentage of found records out of the total records of the same category.

But if you need to this often for a variety of criteria, you might be better off using a subsummarized report.

[David McQueen]

AARRRG

You are so right. I had this in mind from an excesize in getting meta data and made things more complicated than they should have been.

My apologies.

Dave McQueen

[jhogan1]

yes....that worked very easy for the first thing I was trying to do.

Now onto the subfield (breakdown to percentage of field as opposed to percentage of total records)

i am getting held up here:

Get ( FoundCount ) /Count ( field {; field...} )

My only count option is the one you see. I keep changing it to Count (Race::VID) but it doesnt' recognize it.

I know its something I'm just glancing over.

[comment]

It's hard to troubleshoot from here. What does "it doesnt' recognize it" mean? Is 'Race' the name of the second occurrence of your table (Students?)?

[jhogan1]

I'll try to be more clear.

I have a column named "race". I have various columns of various performance data (test scores, GPA's, etc).

What I was able to succesfully do was run a query to find out how many people in a particular race scored below "X" on a test (or above, or whatever).

Now, if let's say I have 250 total count. 30 of the particular race scored in a certain range. It tells me the percentage based on 30/250.

However, if I sort by just race I find out that I have 50 of that particular racial group.

I'd like to also have the 30/50 percentage breakdown on top of the 30/250 percentage that I already created.

It gets tricky because the actual sort I call up isn't asking for the total number of that racial group. It's only asking to identify those members who were in that range I am inquiring about.

My only thought is if I created a secondary table with my racial groups broken down so that it is taken the percentage off of the particular racial group. But, I'd sure rather do it in one function instead of a function for each racial group.

[jhogan1]

well...that's working so far. using your earlier suggestiong. i first strip out the number in the racial group and the import that file on a separate layout and create a joint relationship.

[comment]

The problem here is mixing the found set with the total of the group, which doesn't take into account the found set. It is actually quite easy to do, but the usefulness of such solution is very limited.

What if instead of 250 students today, you will get another 200 next year? So now you find the students that passed the test this year, say 30, should it be 30/450 or 30/200? Same with the internal grouping by race, or any other category. If you count by relationship based on the Race field, you will get the found Hispanics / all Hispanics ever.

That's why I said a subsummarized report would be better. You can group by race, then by grade - and only the found set will be considered.

[jhogan1]

Definitely it sounds like a subsummarized report would be the most useful long term. Unfortunately as I expressed to Dave my programming skills lack something to be desired.

I'm going to study up on what you speak of though because that definitely sounds like something that interests me.