Help with sorting, strange problem.

[Doobeh]

Hi there,

I'll give you a brief overview, I've just finished a database, and one aspect holds date ordered notes in a portal (note,date,user,id_no are the fields in the related file) The relationship is specified to sort the date desending.

Okay. It works, when I enter a new note it gets shuffled depending on the date perfectly. But, and heres the kicker, I imported about.. 10,000 entries into the portal for the clients, for some reason the portal doesnt sort these entries, only new ones. Can you think why? The date is exactly the same format. So basically if i were viewing the portal for X client I might get:

code:

---

Gone Fishing Dean 27/10/2000

Went to meet Bob Mark 25/06/2001

Client agreed to work June 09/04/1998

---

Which obviously isnt sorting it, but if i then added 2 new entries [today and tomorrow] I would get:

code:

---

Gone Fishing Dean 27/10/2000

Went to meet Bob Mark 25/06/2001

Second Entry Doobeh 10/10/2001

First Entry Doobeh 09/10/2001

Client agreed to work June 09/04/1998

---

So you can see, it sorts my new entries, but totally ignores the old ones.

Any ideas you can think of, it's probably quite easy (or I hope it is), but I'm quite unaware of the workaround

Thankyou!

Doobeh.

[LiveOak]

Don't know for sure. You might try forcing indexing on (not just on as needed) for the Date field (in Define Fields, options, storage).

-bd

[Moon]

By any chance are you using FileMaker 3? The first version had a real bad bug in the sort. If so, update to the latest version.

[Doobeh]

Indexing on doesnt seem to make any difference, good idea though, I'm using file maker pro 5.5, It's just beyond me why it doesn't work, maybe I have to somehow put the notes database in order, oh I not sure, a few more coffee's might help *wanders off to kitchen*

Thanks for the help all, keep those ideas coming

Regards,

Doobeh.

[Ocean West]

Try this...

Create another field in the portal (Related File)

Define a calculated number field, to the date field.

cDate = DateField [result number, indexed]

This will generate the numerical value of the date entered from 1/1/0001 till now. Then in the relationship define it to sort decending on this number field.

I think it may have something to do with the field formatting, It appears your formatting is day/month/year...

[Doobeh]

Sounds like a plan I'll try it when I get back to my main computer this evening.

The formatting is day/month/year because I am in England, thats how we deal with them over here across the water, filemaker seems to get the date formatting from the system, so it adapts and works fine (you can still validate via date perfectly).

Thanks for the idea, I'll let you know how I get on you'll either hear cries of thanks and joy, or woe hehe, thanks again.

Regards,

Doobeh.

[Doobeh]

Unfortunaltly it didn't work, I'll explain how I did it, because maybe I carried out one of the stages incorrectly.

go into the notes db, the related one used for the portal, create a new field cdate, its indexed and is a calculation, the calculation is:

dbdate (this is the date field in the notes db)

with the result as a number. I dont think I could have had made a mistake about that. so I assume the problem is with the next part.

I go to the main database's relationships, I have a relationship set to idno = ::idno (unique identifier for each client) then sort the related file by cdate in descending order.

It doesnt actually seem to change the order of anything in the portal, so once again im lost for what to do, any more ideas, or corrections on how I went about the above?

Regards,

Doobeh.

[Ocean West]

Make sure your date field is indeed a date field.

have you tried this on another computer/ Perhaps the version install you are on is buggy. Maby a reinstall?

[Doobeh]

!!

Oh my You hit the nail on the head, I hadn't set the date field as a date field, only the input as the creation date, golly *rubs the egg off his face*

Thank you a great deal, human stupidity should never be underestimated, especially in my case.

Regards,

Doobeh.

[ October 15, 2001: Message edited by: Doobeh ]