Hi everyone,

I'm going to use the word "contacts" to mean occasions on which we have interacted with a student...

I've got a database with that tracks student contacts. The database has two tables: one with student info (name, phone, etc.) and one with the contact info (reason for contact/ date of contact, etc.).

I need to generate a report of all of the students that have fewer than 4 contacts.

Contacts are created by adding a record to the contacts form. In that form, the "Name" field is a drop-down fed by a value list being created by the "Name" field in the students table. I did this because we need the auto-complete, if that makes sense.

So with this setup, how can I find all students who are in the students table, but whose names have been used fewer that 4 times in the contacts table?

Thanks!

Doug

Edited June 26, 200718 yr by Guest

You can create a calc field in the Student table =

count (student::contact name). This will count how many contact records each student has. Place this field on a find screen, enter find, enter <4. This will find students with less than four contacts.

I can't let it slide that you are matching records on Student Name, yikes! What if a student changes his name. Perhaps it is misspelled and you correct it. You've now lost all relation to their contact records.

You need to have an auto-enter serial number for each StudentID in the Students table and put this in the contacts table as a foreign key (_kF_StudentID). THIS should be the relationship between the two tables.

I knew my inelegant solution was going to draw fire :

... but with the proper fk relationship established, will the name field still auto-complete as I type?

Thanks a lot for the count tip. That sounds perfect.

Doug

count (student::contact name). This will count how many contact records each student has. Place this field on a find screen, enter find, enter <4. This will find students with less than four contacts

While it might be right, isn't there any garantee, that it scales particular well, searches made on unstored fields are unindexed ...say you need to know something similar in a supermarked, how many customers are not buying enough, measured for an entire year. A genuine data-mining task!

Perhaps it should be biased somewhat more in the direction of this attachment? Discalimer: would only work with fm8.5...

--sd

mining.zip

Soren,

The summary thing is great, never knew that a related summary would work on only the current related records -- solves a huge issue i've been having.

Cheers.

Interesting method, but I think I would like to see a speed comparison test before drawing conclusions.

So would I Michael, the approach came to all in a sudden, bearing the pre fm7 legacy in mind.

--sd

OK, suppose we have a Parent table and a Child table, and we want to find Parent records where:

Sum ( Child::Value ) > gThreshold

How many records do we need in each table in order to see a difference?

Indeed the sorting to make the GetSummary( to recieve values will take some time ...which will confuse what it appeares to be!

You know what I'm paying attention to:

Anyhow, I asked about this statement and he says he always steers people towards traditional reporting methods (subsummary reports) for speed reasons and flexibility. Unless there is some really great reason to use relationships to simulate a report, don't do it. FileMaker was not designed to aggregate massive amounts of information through relationships. Anyhow, I just wanted to make sure readers were clear on the best approach to reporting.

Snipped from a thread where JMO felt a little weary 'bout this:

http://www.kevinfrank.com/download/repeating-lookups.zip

--sd

I think the problem here cannot be solved by "traditional reporting methods". A sub-summary report will SHOW the sub-total, even reorder the groups by it - but it will not allow you to put criteria on the sub-total.

I am not sure the exact method of converting the unstored to stored matters (another thing to test). I think I would add a simple Replace Field Contents [] to the methods tested - but you haven't answered my question.

My thread has been officially hijacked... :

I'm thinking of a way to produce X records with y portal rows based on random numbers ... Any Ideas?

--sd

My thread has been officially hijacked... :

LOL :(

My thread has been officially hijacked...

No it haven't - we need to question all suggested methods to see if quick fixes to problems, could lead to faulty assumtions, prejudice and bad habits.

Gadflying is not necessarily hijacking, read this:

http://www.fmforums.com/forum/showpost.php?post/224277/

...it's by questioning advisors recommendations, we start a sensemaking process, memorizing is indeed a dicipline if we're talking believes. Why should should we memorize solutions, when we're better off with a thorough understanding???

--sd

Edited June 27, 200718 yr by Guest

I know, Soren... this happens once in a while. Suggestions can spin off into other conversations, that's how it is. The conversation just went down a long rabbit hole before I logged back in. I absolutely appreciate all of the input, trust me. :

I have since set up the appropriate fk relationship, and I am happy to see that the auto-complete is working.

I have also used bcooney's count solution, but the field is returning a zero for all students.

The tables are titled "Students" and "Student Contacts". I put a calc field in "Students" with "Count ( Student Contacts::Name )".

...but each record in the Student table shows zero. Any suggestions?

Thanks again!

My thread has been officially hijacked...

Huh? Do you want a good solution or don't you? If you do, then that means (sometimes) that those who are helping you need to discuss various techniques between themselves to provide the best solution.

In the contacts table, you will now have the field "_kF_StudentID" acting as your foreign key. It could be a popup menu using a value list that consists of StudentID and StudentFullName, only showing StudentFullName. However, with a lot of students, this popup will be way to long. Unfortunately, popup menus do not have the type-ahead feature.

I usually put Contacts::StudentName on the Contact layout, with a Select button next to it. This button shows a popup window with a portal of Student Names. The portal has a filter on it that lets you type in the first few letters of the Student's last name, and narrow down the list in the portal. Then I put a select button on the portal line. Clicking the Select button sets a variable, that with a Set Field script step, fills in my _kF_StudentID.

Does anyone have a link to this technique for our OP?

PS: Yes, finding on an unstored calc can be slow, and I should have advised that with many records, depending on hardware, this could not be the way to go. I am interested in what your tests reveal, Soren, as I would like to guide any user in the right direction. For now, it gets him an answer to his immediate problem.

How did you update the existing data when you put in the serial number key fields?

Just adding the __kP_StudentID to Students, and the _kF_StudentID to Contacts, will not edit existing data. You'll need to manually put the correct numbers in.

What do you mean the auto-complete is working?

I just realized that my current auto-complete solution still relies on a value list, and will not work for me because names do not update in both layouts when changed in the Students layout.

I'm going to try your portal button tip, bcooney... thanks.

our post keep crossing... :

Oh, and I have no existing data... thank God.

Sorry for the triple-post, by the way... I've been out of the forum scene for too long, I guess. :

bcooney, I'm trying to set up that button, but I can't seem to get it. What are the button's settings? There are over 600 names, so you're right about a popup being too long...

Thanks!

Edited June 27, 200718 yr by Guest

Take a look at this example Doug, and let me know if you have any questions. -Barbara

StudentExample.zip

Wow. Thank you soooo much! This is perfect!

I'll go through your settings to learn how you pulled this off.

Now to get that count working... I'll try your suggestion for the count on the file you provided.

Thanks again!

Edit: I just noticed that you built the count into the file, too... I really appreciate this, Barbara. Now to crack the file open and do some learning...

Edited June 27, 200718 yr by Guest

Happy to help. Let me know when you have questions. The example is very "raw" but does show all the necessary techniques, and relationships.

The concern that others have pointed out, is that the count field will be slow when finding, because it is by nature unstored (uses values from a related file). Stay tuned for Soren to dig up a better way.

You could create a list view of Students, showing this count field, and simply sort the records by the count field to group them.

I just realized that I missed something:

The count is important per academic year. This database is for students with disabilities, and we need to track how we serve them year to year. I have a field to input the year of the contact on the contact input layout, so every record will have this value.

This file will break next year, so I need to adjust the count somehow. I'm nowhere near skilled enough to figure out this kind of conditional count... if the solution is too complicated, never mid. I have a year to figure it out, after all. :

Thanks!

I'm thinking of a way to produce X records with y portal rows based on random numbers ... Any Ideas?

--sd

Try this clone.

SearchAggregateSpeedTest.fp7.zip

Comments hunch was correct, no matter how crafty shenanigans we try to cram into the scripting, will it never beat the searching in the unstored field as suggested originally!

But I would let this challenge stand open for eager "Champions" ...is there a crafty algorithm out there that outperform the search in the unstored? - with the present version of filemaker. It seems that earlier on did it make much more sense.

I have here dared to make a fool out of myself, but to question hunches, and established methods ...can only make us wiser!

When trying to beat it, did I learn that other refinements than Comments to the algorithm could make sense as well:

Instead of deleting every record in the SD Table occurance, could it be utilized that every import makes a found set, enough to make the GTRR(FS) work, but it leaves a messy file with a whopping mass of useless record ...to delete occationally perhaps.

But the lions share of irritating processing time seems to be the import between tables!

--sd

I hope you have corrected my mistake of leaving out Show All Records[] before Replace Field Contents[] in the second script. In any case, my findings are similar to yours: searching on unstored was fastest. What's more, subsequent searches were MUCH faster - even with a different threshold!

Caveat: this was in single-user mode; a served solution might be different.

But I would let this challenge stand open

You could try testing GTRR using Ugo's method of relating on unstored. But that would be somewhat limiting, requiring a change to the calculation formula for every type of search.

What's more, subsequent searches were MUCH faster - even with a different threshold!

Yes I read this before somewhere, but as far as I remember is it here:

http://network.datatude.net/viewtopic.php?t=102&highlight=fuchs

You could try testing GTRR using Ugo's method of relating on unstored.

I've thought about it, but there did we ditch the found sets option we had with GetSummary( ...or am I interpretting your hint wrongly??

--sd

am I interpretting your hint wrongly??

Maybe. What I meant was a calculation field in Parent (result is Number) =

Case ( Sum ( Child::Value ) > gThreshold ; ParentID )

then match this to ParentID of a second TO of Parent.

To do the "find", show all records and GTRR [related only ; match found set]. I realize this may be a third-generation bastard of Ugo, but the family resemblance is there...

Just tried it, BEACHBALL, instead of barbershop ...

--sd

Ah. Thanks for trying - I meant to do so later, but you have saved me the trouble. It's not very practical anyway, but I thought it was worth a shot.

The thing is, there HAS to be a search on unstored SOMEWHERE, because the required information is unstored by nature of normalization. So all these "crafty shenanigans" are merely shifting the same search to another location. Better bite the bullet and do a find on unstored directly.

I suppose with a huge amount of records and often-needed finds, some form of de-normalization might be called for - but I guess that's nothing new.

I was so hoping that you guys would come up with a workaround. It's very much like designing an inventory system, either your numbers are relational or transactional. He could have his script increment a counter for each contact record made, thereby allowing a search on a stored counter field. But, transactional processes can lead to errors, whereas relational values seem to deliver more accuracy. I remember a wonderful thread by LaRetta on this very topic.

I'll give it a look.

OK. You have really three approaches:

1. Dedicated relationships per year, with corresponding calc fields for the count per year.

2. A global popup that generates a count for the year that is chosen in the global.

3. A summary report.

In the attached example, I have all three. Take a look at the scripts menu for the Summary Report, by Year, by Student. It is nice to have the dedicated fields (approach #1) because you can use them for charts either in FM with Flash Charting or exported to Excel.

StudentExample2.zip

wow, Soren pointing to a demo involving repeating fields !

Well, glad to be back indeed LOL

wow, Soren pointing to a demo involving repeating fields !

Well, glad to be back indeed LOL

He must be sick or maybe the planets alligned.

/Looks out window

Interresting topic, Doug, thanks for letting this discussion spread to some other challenges...

As you say, Michael, if this "find process" becomes a daily routine task, the delay becomes problematical. If not, let the user wait for an answer as long as they don't get the beachball.

Even if the replace field content method would have demonstrated to be quicker, could it really be used in the real life ? Each find would result in a change in the modification log for each record, as first reason I can find for not using it. Plus the problems known in network situation involving this command.

For this same reason, indexing a previously unstored calculation also brings a modification to the record.

My idea is that there is a reason to have this result unstored. Whatever the method to display the result ( an aggregate cal or a summary field from a table occurrence of the child table ), what we see is the result of a relation. What if the relation was dynamic enough to filter the child records from a date range.

ex :

Identify those customer owing more than 1,000 $ in a filtered set of child invoices from may to june only.

Again, if this search is routinely performed, then, it has to be scripted and optimized so that the result is shown quicker. If not, let the user go take a coffee and let us wait for a filemaker version in the future where the search engine will really be performant enough to get the expected result that fast.

By experience, I use aggregate tables to store these aggregate results. For Products for example, the inventory is indexed but the result is not stored in the product table ( in order to prevent record locking when an updating script is triggered to pull the new inventory levels ) but in a Inventory table where each product has its own record.

One could consider the product record is modified anytime the inventory level change. I don't so I can't accept to have my "log" changed on that record anytime a sale is made.

And my so loved repeating fields to store any aggregate calculation for any of my customers for the last 12 months in an aggregate table.

Repeating fields still are quicker than relation.

Booooohh, nobody understands me :P

Edited June 28, 200718 yr by Guest

Maybe. What I meant was a calculation field in Parent (result is Number) =

Case ( Sum ( Child::Value ) > gThreshold ; ParentID )

then match this to ParentID of a second TO of Parent.

To do the "find", show all records and GTRR [related only ; match found set]. I realize this may be a third-generation bastard of Ugo, but the family resemblance is there...

No comment on the bastard thing

But...

Just drop the calculation

Case ( Sum ( Child::Value ) > gThreshold ; ParentID )

in an empty layout

go to that layout

Copy All Records

Paste back into a global

Go to related from that global to the parent ID

Who said tricks from FileMaker 4 cannot be used anymore :

He must be sick or maybe the planets alligned

NO not at all! My problem is that advisors can't stand the chance, of delivering the cute and clever in replies, without the anxiety

....that such methods might be memorized by a newbe or two who can't be skilled enough to see if his/her solution is normalized enough ...newbes who feels utterly accomplished when making bysantine scriptings or pulls the database as such out of it's realm.

A fair share of the questions goes like "why can't it be more spread sheet'ish" ...because the known metaphor is used to understand a new one! This is why I feel repeaters should be tutored by cautions!

--sd

aah, my Soren is back to earth :

Nice to see you back :P

Edited June 29, 200718 yr by Guest

Who said tricks from FileMaker 4 cannot be used anymore

I tried that. My test file has 20k parent and 60k children records. Searching on unstored takes about 29 seconds the first time, 5 seconds on subsequent searches. Your method takes 9 seconds (copying all those records...). So it's neither here nor there. But the real issue is that the criteria needs to be hard-coded into the calc (please don't bring up Evaluate).

All in all, I agree with what you said in the post before that - even if I don't understand you. :P

Nice to see you back

It's me who should say so, I havn't made excursions to neither outer space locations or similar! It you who are returning...

My reservation is as usual, if reasoning is made to fit believes creationist'icly or submissively like St. Pauls letter to the Romans dictates, is it not likely to produce the best kind of engineering, be it in Filemaker or similar tools where the "questioning" is an urged disipline.

--sd

even if I don't understand you.

Not a problem. I too don't understand Soren's "Romans letter to St Paul dictates" but I still like the readings from Copenhagen :

Edited June 29, 200718 yr by Guest

For those who wish to poke into this Paul business:

http://www.anthonyflood.com/sellerspowersthatbe.htm

...perhaps isn't as much what he believes, as the way someone uses it. Similar to repeaters, plugins or global fields.

--sd

Thank you so much for posting this file! I have been stuck with the poor options of a popup menu or a dropdown list when I have a value list using field contents from over 1000 records in another table. Mine runs over a network with FMS so the timing of keystrokes when trying to use the autoenter in the value list can be very frustrating depending on network traffic etc.

This looks like a great alternative, but I really wanted a 'real time updating' filter like I've seen in non-FMP databases (excuse my beginner terminology).

To do this, I tried a script that keeps comitting the global filter field and then going back to it so that the portal appears to display the related record 'as you type'.

I've attached my attempt. Can any of you pro's suggest a change or improvement as it beeps at me sometimes (maybe my keystroke happens at the wrong time during the script loop)

TIA

Phil

StudentExample2.fp7.zip