Find all Students with Less Than 4 Contacts...

[Doug.app]

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, 2007 by Guest

[bcooney]

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.

[Doug.app]

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

[Søren Dyhr]

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

[Genx]

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.

[comment]

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

[Søren Dyhr]

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

--sd

[comment]

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?

[Søren Dyhr]

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

[comment]

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.

[Doug.app]

My thread has been officially hijacked... :

[Søren Dyhr]

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

--sd

[AudioFreak]

My thread has been officially hijacked... :

LOL :(

[Søren Dyhr]

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, 2007 by Guest

[Doug.app]

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!

[LaRetta]

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.

[bcooney]

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.

[bcooney]

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?

[Doug.app]

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.

[Doug.app]

our post keep crossing... :

[Doug.app]

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, 2007 by Guest

[bcooney]

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

StudentExample.zip

[Doug.app]

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, 2007 by Guest

[bcooney]

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.

[Doug.app]

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!

[comment]

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

[Søren Dyhr]

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

[comment]

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.

[Søren Dyhr]

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

[comment]

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...

[Søren Dyhr]

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

--sd

[comment]

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.

[bcooney]

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.

[bcooney]

I'll give it a look.

[bcooney]

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