Portal not sorting by date properly

[db_tragic]

Have a portal pointing to an Invoices table, which accumulates all invoices across a number of an individual client's animals that I have treated.

 

Whilst sorting the portal by date (in descending order), it is sorting the invoices by month, however the day order isn't always correct, and I can see no pattern suggesting why this is so. I have checked the date formatting for the date fields, and they are correct (dd/mm/yyyy). I don't have any second level filtering applied at this time ( e.g. by animal name) - which, incidentally, adding this level does not help matters.

 

 

Any suggestions thanks?

[jbante]

Are you sure that the date fields in your portal have a data type of date? Storing date data in a text or number field can lead to incorrect date sorting, though I'm not sure why the sort by month should still be correct if your date format is dd/mm/yyyy. Can you post a screenshot of the sort order you are seeing? Maybe someone else on the forum can figure out what the pattern is, and therefore what the problem is.

[LaRetta]

I have two other thoughts (thought not as likely) ... a possible mismatch of date format data  (which would be allowed by FM during an import) or the date field being displayed is from a different table occurrence than the portal or is an incorrect field.

Edited December 17, 2013 by LaRetta

[db_tragic]

Found the problem!

 

jbante - wasn't to do with the date formatting (already had that in place).

 

The portal included a date field from the Invoice table, which is itself, a 'lookup' field, pointing to the date in the Visit Notes table.

 

Not sure why this causes a sort problem, but anyway…….

 

I did up a new portal, using the date field from the date field in the Visit Notes table, and then pulled the other fields I already had displaying from the Invoice table.

 

Sorting problem solved. 

 

It appears that fields in a portal that are to be sorted, at least in this case, should be 'original' fields, and not 'lookup' fields.

 

You were right on it LaRetta "date field being displayed from a different table occurrence" ( wrong table in this case).

 

Thanks for the input folks

 

Chris

 

 

 

 

Are you sure that the date fields in your portal have a data type of date? Storing date data in a text or number field can lead to incorrect date sorting, though I'm not sure why the sort by month should still be correct if your date format is dd/mm/yyyy. Can you post a screenshot of the sort order you are seeing? Maybe someone else on the forum can figure out what the pattern is, and therefore what the problem is.

 

 

I have two other thoughts (thought not as likely) ... a possible mismatch of date format data  (which would be allowed by FM during an import) or the date field being displayed is from a different table occurrence than the portal or is an incorrect field.