Sort Order Number

[dechetes]

I am using a filemaker database to hold records of people on a waiting list for their children to attend one of our 3 pre-schools. When customers enquire they select one or more sites they would like their child to attend.

When customers call up they want to know what position they are on the waiting list.

What I want to do is be able to have 3 fields, one for each site, on the customers record page that gives the position on the waiting list from the result of a 'Find' by that site and sort by date added (customers are put on the list based on a first come first served)

The idea is that a person would sign up for Site A, Site B but not Site C and on their record would be:

Site A - 45 on the list

Site B - 12 on the list

Site C - or 'not enrolled' etc

I have tried using record numbers but these are unique and dont change when choosing specific records based on the results of a find

Any suggestions would be much appreciated!

[jteich]

Hint: it is possible to create three selfjoins (one for each school) which match only for those people waiting in front of me in the queue.

If you have this relationship, the rest is easy counting.

count( rel::id )

-jens

[sbg2]

Her's what I came up with using a script in FM v7. With FM v8 or so you can probably use variables instead of global fields.

WaitList.zip

[jteich]

Very complicated script.

I think the solution with the relationship is much easier.

See modified example.

Jens

WaitList2.zip

[comment]

It would be even easier (and more useful in many other ways) if each registration was a separate related record.

[jteich]

Correct, but then you should have separate tables for customers and registrations!

Jens

[comment]

That was precisely my point.

[dechetes]

Thanks for that, its exactly what I am after - the only question is how do I integrate that into my existing database? The script seems quite complex - can I copy the script into my existing database and if so what new fields do I need?

At the moment I have

- Name

- Enquiry date

- Enrolled? - Y/N

- which schools they are interested in

I can adapt it so that when the box for a school is ticked, it copies the enquiry date into that schools 'waitlistdate_schoolX' field (which is hidden) and allow that script to work - what else do I need to do?

Thanks again, I am surprised and happy at how quickly this community responds to the questions of a Novice!

[sbg2]

What comment says about seperate tables bears considerable.... well... consideration. The information really should be stored in a seperate table. Whenever you start using field names with postfix numbers (ie. school1, school2) the data most likely belongs in a related table.

Another thing to consider is that the solution provides a customers actual position. Right now if two other people would sign up today for school 1 then jteich's solution would show that each new customer is number 6 on the list. The first customer should be position 6 and the next should be customer 7. One way to account for this would be to use a time stamp field.

I used the ID_Customer as the determining factor of who comes before who. This could also be flawed as Bucky Buchanan may decide on 1/3/07 to add his child to the School 2 list. If two other customers had already been added to the School 2 list that day then Bucky's kid shouldn't jump to the front of the list. Again a timestamp field would help solve the issue.

I didn't think the script was that complicated but it may be beyond your experience level at this point. jteich's solution is a good start. Adding a calculated field using the Count function would provide the number(s) you are looking for.

I got tired of cluttering up my relationship graphs for calculated fields and am using scripts more often to perform my calculations. YMMV.

[dechetes]

Thanks for the input, I will have a go with both solutions but relationships may be wise as we have over 300 people on the waiting list with 5 - 6 new ones every day, is the sugestions to have 3 tables, 1 table for each school all containing the same data eg name, address phone no. etc and create relationships, only difference would be date added to the list. Would I then create a separate table that shows the shared fields (name etc) and then uses the start date in the manner that jteich came up with?

[comment]

The suggestion is to have one table for Schools, one for Applicants, and one for Applications. The last is a join table between Schools and Applicants, so in effect an Application record could look something like:

ApplicantID: 123

SchoolID: 2

Date: 6/6/2007

[dechetes]

Would this allow applications to multiple schools eg

ID: 123

School: School 1 and School 2

Date: 12/03/07

And if so could it be done by just using a tick box for ease of entry?

[comment]

No, that would be 2 separate records - that is the whole point.

I am not sure I understand the second question. It would be easy enough, I think, to create applications in a portal to the Applications table, placed on a layout of Applicants. If the relationship is allowed to create records in the Applications table, all it takes is selecting the school and filling the date. Actually, the date could be filled automatically.

[dechetes]

Thanks I think creating a portal on the applicants page makes the most sense. I don't think I am quite there with what has been suggested so far but I am much further along. Many thanks for the help