Jump to content
Sign in to follow this  
gwent

Top "N"

Recommended Posts

gwent

Good Morning All,

I've been searching "Help" and Forums for some advice on creating a Top "N" type Report. I have a Inspection Form and wish to display the Top 5 missed Items in a report.

 

Since I have been unable to find a Top N function I thought perhaps of doing the following:

 

Create a Count for each Item missed

Create a Portal View based on Inspection Layout and rank Count Missed in descending order.

I would set Portal to display 5 rows with no Scroll Bar

 

The downside to this is that the Inspection Table now has in excess of 400 Fields and the new Count Field will add many more.

 

Is there a better way to do this.

 

Thanks, confused.gif

 

gw

Share this post


Link to post
Share on other sites
holzensp

I'm no hero at FM by any means, but from what I've seen there's no equiv. to the SQL "LIMIT 5" sort of construction. What you could do is run a script to calculate the contents. In that script, write a loop that looks a bit like this (not official FM syntax, variable-assignments require some function - see your scripting window, N is your top N constant):

 

loop

count = count + 1

if(count > N)

Omit Record

else

Go to Record/Request/Page [Next]

end if

end loop

 

That will leave you with a "Current Found Set" of N records.

 

Hope this helps.

Share this post


Link to post
Share on other sites
gwent

Would this script "look" at all the Inspection Fields?

Share this post


Link to post
Share on other sites
holzensp

I don't really know what you mean by "looking at fields", I assumed you already had a 'Current Found Set' of all missing items and wanted to limit that set to the first 5. Whenever you have such a set, you can just run the script I typed on 'the current set' and that will take the first 5 - you might want to put a Go to Record...[First] at the beginning.

 

In short: if you have a set of records and want the first 5 of them, why would you want to look INSIDE those records?

Share this post


Link to post
Share on other sites
gwent

I'll check out what you've suggested.

 

The found set would be for example all Inspections on a given Restaurant or all Inspections for all Restaurants [11] for a given period of time.

 

The result would hopefully display the 5 most often failed Inspection Items in the found set.

 

What this tells me is I really needed to go to FileMaker School.

 

gw

Share this post


Link to post
Share on other sites
holzensp

No worries smile.gif

 

Keep your eyes peeled for someone who actually knows FM well enough, I might very well have overlooked something... can't seem to let go of the "server DB" way of doing things (why is there no SQL-frontent for FM??).

Share this post


Link to post
Share on other sites
-Queue-

Answered question on ISO FileMaker thread.

Share this post


Link to post
Share on other sites
gwent

Thanks, I read your other post and will give it a try.

Share this post


Link to post
Share on other sites
Maarten Witberg

-queue-, do you have a link? cant find it...

 

thanks

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
Maarten Witberg
The result would hopefully display the 5 most often failed Inspection Items in the found set.
I have been thinking about this. -Queue- says (over at Iso FM) that his solution gives the five restaurants with the largest number of failed inspections which is not the same as the most often failed inspection items.

 

I understand there are 400 items. Here's my solution to get a total of missed inspections per item without making 400 sum( ) statements.

 

i tested this for a list of six items, i'm hoping this would work the same and not too much slower for 400.

 

I'm assuming the items are checked with a fail flag or a pass flag, I am assuming this is "f" or "p".

 

create the following fields:

_matchall=number=auto enter 1

serial=auto enter serial add 1

previous_serial=calc=serial-1make a relationship based on previous_serial::serial, this links all records to their previous ones in a chain. name the relationship "sj_previous"

failedstring=text result calc= item1&item2&item3&item4&....&item400

flag_position=400 repeater=fill this (only once) with 1,2,3,4,5,6,....,400 #you can make this auto-enter the looked-up value of flag_position using a self join relationship based on_matchall. a global would not work because then the next calculation will not work.#

all_in_one=Position(extend(failedstring;"f";flag_position)=flag_position #this boolean calc puts the number 1 in any repetition that has a matching '"f" in failedstring.

running=calc 400 repeats=previous_running+all_in_one

previous_running=auto enter lookup number, 400 repeats=sj_previous::running now this is a loop. therefor it's a lookup relation not a dynamic one. also in defining you have to first define previous running but not the lookup, then running, then the lookup for previous_running.

 

What this results in is a running total of fails per item for the whole database. only the last record created gives the correct number!

if you want to do a total of fails for a found set then you have to do a script in two steps:

1) reserialize the found set so that it does not fit in the chain

2) do a relookup based on "serial"

 

now you still don't have a top N result.

maybe you can build a script that extracts the max value, then the next max, then the next and so on until N.

 

hope this helps.

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
-Queue-

Good call Kjoe. If there were a table/file with all possible items, then you could use the same idea I did and get a list of the top N items in the Items table.

Share this post


Link to post
Share on other sites
Maarten Witberg

That would make life easier.but I guess that would mean thrashing what there is now and starting from scratch. don't know if that's an option for gwent.

 

so I have been knitting on following my previous post to get the top N from the repeater called "running". lo and behold, I succeeded. what it does is loop through the repetitions and then compare the value in the repetition to that of the current "top" field and the previous one and then decides to copy that value or not.

 

the following fields are needed for a top 5 script:

g_counter = global number, set to 1

top1 thru top5 = global number fields

ref_top_1 thru ref_top_5 = global text fields #to store a reference for the found repetition number (so you know where the number comes from)

 

the script runs like this:

sort[restore, by serial, ascending]
go to record[last]
set field[top1;0]
set field[top2;0]
# etc thru top5
set field[ref_top1;"""]
#etc thru ref_top5

#now loop thru repetitions of "running" to get top N by iteration
loop
if[GetRepetition(running;g_counter)>top1]
  set field[top1, GetRepetition(running;g_counter)]
end if
if[GetRepetition(running;g_counter)>top2 and GetRepetition(running;g_counter<top1 ]
  set field[top2, GetRepetition(running;g_counter)]
end if
if[GetRepetition(running;g_counter)>top3 and GetRepetition(running;g_counter<top2 ]
  set field[top3, GetRepetition(running;g_counter)]
end if
if[GetRepetition(running;g_counter)>top4 and GetRepetition(running;g_counter<top3 ]
  set field[top4, GetRepetition(running;g_counter)]
end if
if[GetRepetition(running;g_counter)>top5 and GetRepetition(running;g_counter<top4 ]
 set field[top5, GetRepetition(running;g_counter)]
end if
exit loop if[g_counter=number_of_repetitions_in_running]
set field[g_counter;g_counter+1]
end loop
set field[g_counter;1]

#now loop again to obtain item references#
loop
if [GetRepetition(running;g_counter)=top1
  insert calculated result[ref_top1;g_counter&"¶"]
end if
if [GetRepetition(running;g_counter)=top2
  insert calculated result[ref_top2;g_counter&"¶"]
end if
if [GetRepetition(running;g_counter)=top3
  insert calculated result[ref_top3;g_counter&"¶"]
end if
if [GetRepetition(running;g_counter)=top4
  insert calculated result[ref_top4;g_counter&"¶"]
end if
if [GetRepetition(running;g_counter)=top5
  insert calculated result[ref_top5;g_counter&"¶"]
end if
exit loop if[g_counter=number_of_repetitions_in_running]
set field[g_counter;g_counter+1]
end loop
set field[g_counter;1]

the item references are the repetition numbers, these correspond to the order of items as defined in "failedstring".

probably you could obtain the item name from there but i cant think of any way right now.

if there are ex aequo ratings then the item reference will be a "¶" delimited multiline.

 

of course, this is a v6 solution. it may be possible to build a custom function that does all this calculating in v7. challenged, anyone?

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
gwent

WOW, you guys are something else. I really do appreciate your input. I only understand the barest minimum of what you are saying. As a newbie to FM I am trying to relate the info to my experience with Crystal Reports and Access / SQL. In theory I understand but in FM practice I barely have a clue.

 

I'll experiment with your ideas [ I copied them to a text file for further study] and will most likely incorporate them as I better understand FM. I will most likely, for now, add a Counter formula for each Inspection Item because each is a Check Box with various point values depending on the nature of the Inspection Item. I can them sort the count and display the Top 5 in a 5 row portal (I think).

 

I am gonna find a FM class [Foundation II] as soon as possible and join a User Group [FMPUG is closest in Orlando about 200 miles north].

 

Again thanks for your help and ideas and perhaps with study and practice I might even in time understand them.

 

greg

Share this post


Link to post
Share on other sites
Maarten Witberg

Hi Greg,

 

Thank you for all the thankyous, but I get the feeling that it has not been much help at all.

On the one hand, I get the feeling that you have a pretty good idea of what you could get to work yourself. On the other hand, I think that, from what you told, you are making a database that is going to be hard to manage. Keeping track of 400 user entered items in a single record seems horrendous task. And if at some point in time the inspection items change, you're faced with a new big task. And you'll be sent into the forest with more knitwork like I provided above.

 

If I understand correctly you have an inspections db and a restaurants db. Like -Queue- suggested, if you have a separate items file/table, you could much more easily create reports based on found sets of items, be they of all items for a restaurant, all restaurants for a single or a few items etc.

 

How far are you with developing your solution? or is it an existing one with lots of data already in place that you're modifying to make reports?

If you're not too far into it yet, I suggest you do consider to restructure the whole thing.

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
gwent

Thanks kjoe

 

It's still in the development stage and yes changes are occuring but I think they have settled on the inspection items "for now". The only Records are a few samples I have been using for testing calcs and such.

 

The structure at this point is a single file with 3 Tables, tblQSC [The Inspection form w/ Inspection Items, Sums, Score Calculations etc.], tblRestaurants [Name and misc information], and tblManagement [Restaurant Managers, Supervisors, and Support Centre Inspectors].

 

My plan is to base Reports on found sets which could be based on Restaurants, Selected Items, and most likely Individual Mangaers.

 

I will try and send you a private message with an idea.

 

greg

Share this post


Link to post
Share on other sites
Maarten Witberg

Hi Greg,

 

I am going to give you my views on the setup and then you must decide what you do with them. And hope that some v7 wiz will jump in to correct me if my contentions are wrong, as I am versed in v6 not 7. However I believe that good database setup is version independent. the principle of relational database has not changed.

 

If I understand correctly we're talking about a restaurant chain and the head office of this chain wants to inspect performance for cleanliness etc.

the objective user of the database is a restaurant manager who inspects only his own restaurant or part he is responsible for or a head office staff person.

so all items should be there if the user says "new inspection". he would then have to specify which date, which restaurant, and which level he inspects (his restaurant part or a whole restaurant). Submanagers should be linked to specific items, so a front room mgr would only be linked to front room items. he'd only have to select his actual name or employeeID or whatever.

then there's the report stage and these have to be split as I understand it between various managerial layers. so a summary report and a sub-summary etc. I guess you're on the right track using portals for OSD. But for printed or PDF reports you'd have to go back to found sets of the line items table and that is your items list.

 

In setting up this system I would start from the smallest unit of information, which is the inspection item in your case.

 

so the setup looks like this:

 

1. an item names table with name and score references and references to manager type. you could add records to this item names list later on, or check items as no longer to be used (but still operable for old inspections). the big advantage is that your old reports will look the same if the inspection changes over time and your new inspections will not be burdened with obsolete items.

2. a list items table containing the actual inspection data. item names and score lists are looked up from tbl one. Basically 400 records are created for each inspection in a script, either as a step by step (user fills in list one record at a time) or as a full list which, treated as a found set, can be browsed by the user.

3. an inspections table that holds (portal based) reports on individual inspections

4. a restaurants table that holds same.

5. a subaltern managers table that holds same.

 

so 3-4-5 are just reports tables, no actual inspection data is ever entered in fields of these tables.

you could make do with just an inspections table and create found sets / filtered portals per restaurant, manager etc. I guess this depends partly on taste and on access level issues.

 

In any case the list items table would have the following ID fields for each record:

 

SerialID (its own unique code)

InspID (from the inspections table)

RestID (restaurants table)

TopMgrID (employeeID for top restaurant mgr or staff person doinh more than one full restaurant inspection).

ItemMgrID (employeeID for item specific manager)

ItemNameSerial, an auto enter serial that runs from 1 to 400 with each script step thus looking up the correct item name and score reference.

 

based on these IDs you can make reports based on found sets and do topN tricks like -Queue- described here and on ISO FM

 

if you want comparisons between inspections you'd have to create a found set containing both inspections and then make a comparison, e.g. on both topN lists.

 

I'm doing this from the top of my head so please look critically into any and all suggestions.

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
gwent

Hey thanks kjoe,

 

I'll work with what you have suggested as this is still in the work up phase. In fact I just received an e-mail today requesting I add Training info to this db for Managers and Line Staff by connecting to Inspection results.

 

I have received some interesting feedback thus far on this topic and will experiment with what I understand and attempt to learn what I do not understand. FileMaker appears to be a very extensive db application developmnet program and by all indications I have much to learn.

 

I'll keep you posted on my progress and thanks a bunch.

 

greg

Share this post


Link to post
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.

Sign in to follow this  



×
×
  • Create New...

Important Information

Terms of Use