sorting~

[saralee]

Hi everyone, I want to include a sorting function in my script, and here is some background info.

I have a household table that is linked with a baby table.. For each baby record, I have fields with specifications I would like to search on, e.g. languages, age etc.

And I also created a customized search function for the users - users can search households that have babies who, for example hear 100% English and are under 10 months ago... The search works perfactly, but afterwards the sorting function does not sort the baby records properly. I want to enable the sorting function implemented as part of a script to sort the babies in the found set by age, but it does not sort properly with households that have more than one kid. The siblings' ages of the selected kid will "mess up" the sorting. In my script, I just sort records and pick the records by the full age field in the tblBabies.

The thing is that the search result has to be shown in the household layout. So I just wonder if there is any way to sort the records by the selected/searched babies only.

Hope it makes sense. Thank you!

[Lee Smith]

Why not use a relationship and a portal?

[saralee]

Yes, this is a relationable database and babies is a portal in the household, but for some reason, the sorting by babies' age is not correct after the search...

[Lee Smith]

Your portal has rules that it is follows, and is overriding your search criteria.

I'm not really sure I'm following you though, but it sounds like you need to create a report or reports in the babies file.

[saralee]

Thanks, Lee. I'm not sure about the reports part. The final product I need to present to my users is a sorted household records within a search constraint.. so bascially search -> sort -> viewable in browse mode..

The tricky thing is that my sorting is based on babies' ages and these babies have to be in the search constraint first..

[Lee Smith]

You can sort a portal by a second field, do you have that checked?

[comment]

No, you cannot sort households by SOME babies' attribute.

the search result has to be shown in the household layout.

Why?

[saralee]

sorry that is just our design which relates to our operation.. we want the users to view the household as the search result..

oh.. so it is not feasible.. too bad.. anyway i can do the sorting in SOME babies in the baby layout first and then display all related household in the household layout?

Thanks a lot! this is getting so specific..

Edited August 18, 2010 by Guest

[comment]

What you could do is sort the found babies (in the Babies table) and show them on a layout (of the Babies table) with fields from the Households table. However, you need an extra step to remove duplicates (i.e babies from the same household). Unfortunately, I don't see a method to do this other than looping.

Edited August 18, 2010 by Guest
It wouldn't work.

[Lee Smith]

Hi comment,

That's what I said in an earlier post.

I didn't give any details, as I figured that once the light bulb went go off in her head, she would followup with some how to questions.

Lee

[saralee]

thank you both Comment and Lee!!

So I need to clarify some steps here..

What you could do is sort the found babies (in the Babies table) and show them on a layout (of the Babies table) with fields from the Households table.

after my searching script, I need to go to layout baby and do sorting, right? And then I need to create a report based in the tblBabies? with information pulling from the household? I'm lost here.. Please advice. Thank you!

[comment]

I will clarify, but first you need to explain something to me about your goal:

Suppose you want to find households that have babies who hear 100% English, and sort them by the age of those babies. And suppose there is a household that has TWO such babies of different ages - for example 5 months and 17 months.

Where in the sort order should this household appear? Between 4 and 6 months - or between 16 and 18 months?

[saralee]

Thank you comment for the question. i think it does not matter which age range we will put this household.. it could be put either place, since the user will figure out which sibling is in the order of the current sorting..

so would it make things a little easier?

Thanks!

[comment]

LOL, if it doesn't matter, then why does it matter?

Anyway, this is what you need to do, in big steps:

1. Find the babies of interest (in the Babies table);

2. Sort them by HouseholdID, and omit duplicates (i.e. leave only one baby from each household);

3. Sort the remaining babies by age (or whatever you need);

4. Display the results on a special layout of Babies that contains only (or mainly) fields from the Households table.

[saralee]

Thanks, Comment! In step 4, did you mean I need to create a report based in table babies?

[comment]

It doesn't have to be a report - it could be just a list/table layout they can browse. But it must be a layout set up to show records from the Babies table.

Of course, it will look to them like it's a layout of Households - but there is no way you could sort the Households table by the found babies' age.

[saralee]

Thank you, comment!

I found out there is a general sorting issue here - even for a babies set that are all single child, the sorting function somehow separates the data sets into two parts, each of the set is sorted but the total order is messed up.. for example, below is a set of age of babies i get (all of them do not have siblings)

4m2d, 4m5d, 5m10d...5m22d || 3m27d, 4m3d, 5m4d...

and after the sorting, in the browse mode, the first record that shows up is 3m27day in this case which is after the separator i indicated above..

seems very strange.. do you have any ideas? THANKS :)

[comment]

I don't understand what you're describing. IIUC "3m27day" SHOULD be the first record after sorting. What is this "separator"? And what is your sort order?

[Vaughan]

How does the whole "4m23d" age calculation work? This has been discussed MANY times on the Forums, and it not optimal because the length of a month changes. For instance could 4m30 days be valid if the month has 31 days? And would be be equal to 5m4d if the month has 30 days?

My suggestion would be to have a field that calculates the babies age in days. Users need not see it: you can keep displaying the XmYd field if you must. Use the age-in-days field for everything else like sorting.

The calc would be unstored and return the result as a number:

Get( CurrentDate ) - DateOfBirth

[comment]

Well, if age is calculated as the age at current date, you could sort by DOB.

[Vaughan]

Um, yeah, you could. And it would be stored. :)

[saralee]

Thank you both! You are correct. Sorting by DOB works! :)