BUG : FMP thinks NULL is Higher than a value

[genevieve charbon]

Hi,

I've a layout in list view, 2 columns, sorted to the "sale of this month" column by decreasing order.

Product Name Sale of this month

Product Y

Product Z

Product A 10

Product C 7

Sale of this month is in fact a related field to an order table and qte, so when there's no orders there's no row with that product sku and hence no quantity = no related field. So the relation returns nothing.

The problem here is that fmp treats the void values as if there were > to any other number.

Is that normal. How to solve this, without modifying the relation, the related table, and without unstored calc.

Is this a bug this seems so.

DON'T write me a thesis on how to do stats, ordering tracking etc. Just focuss on Null vallue considered higher than any value (it should be considered as the lowest value)

Yes I double checked the sale qty field is indeed stored as a number

Isn't this a bug ? as anyone else noticed it ?

Edited December 13, 2007 by Guest

[David Jondreau]

Your experience doesn't echo mine.

You've sorted the records, but is the relationship sorted?

sortNull.fp7.zip

[genevieve charbon]

No my relationship isn't sorted, and I don't want to because it's also used in other parts of the solution. And anyway I want to sort on the fly with column sort in table view (I wrongly wrotre list view while I meant table view).

Anyway, your sample has no problem on my machine either, but I noticed your sample only use one table (I agreat it should be the same).

So I made my sample using 2 tables. And the "bug (?)" shows indeed.

So maybe that's because the bug only occurs with separate tables.

I then investigated with language isseu.

I use FMP advanced 9v3 in english on a Mac OS X set in french. So, sorting orders is set by default on French sorting rules.

That's ok.

But I don't feel that french rules consider null > to values.

So I tried to change the sorting rule without success (stays greyed)

So I recreate the sample from scratch using Mac Os X in english. Sure enough the sorting rule was set to english.

So … ?

The bug is still here, so that's not a localization issue.

I think thats for relationship accross tables.

Finally, I noticed this stange behaviour either since 7 ou 8.5. What I know is that in the past there wasn't taht bug, then I upgraded and thenthat bug occurs.

Please have a look to my files (they're the same aside sorting rule language)

SortingBug_.zip

[genevieve charbon]

Oh my God, it seems I've really found a bug !

I've redid my sample using one table (not really usefull in real likfe), and it sorted ok

So my findings

If your relation is between 2 different tables, when sorting table A from a numerical value of Table B, Filemaker will order it placing NULL values on top in descending order.

On the other end if you you replace Table B by a table occurence of Table A, then no problems NULL values are on the bottom in descending order.

as FMP manual states in "Sorting records troubleshooting

" that :

Records with nothing in the sort field sort to the top of the list in an ascending sort.

I think this is indeed a bug.

see my file with the self relationship which has no problems, contrary to the previous samples involving 2 tables I've just sent before. I've created that sample fully in englisjh with english sort order to get rid of any possible localization issue.

Pself.fp7.zip

[comment]

There's a difference between an empty related field and no related field.

ProductsAndOrders_english_made__-____.fp7--.zip

[genevieve charbon]

Ok, thanks for pointing me the difference. So the self sample probably works because the record it relates to does exists while on my two tables solution it doesn't.

There's a difference but that difference shouldn't matter, should it ?

It seems illogical to me that no value is higher than a value.

I've edited your file and put an unstored calc that gets the related qty, and then it works.

To me there shouldn't be any difference on a sort to an actual related field or a sort to it as an unstored calc.

Don't you think the behavior is bogus ?

coment_genv_edit.fp7.zip

[comment]

I think it's a very useful distinction. I don't know if it's done on purpose or just a by-product, but it makes a lot of sense to me.

[Vaughan]

Could the original problem be solved with a calculation field that returns zero instead of null?

I often add zero (literally "expression + 0") onto some calculations to force them to be zero instead of null.

[genevieve charbon]

Comment,

in what situation would you think such a strange sort order would be beneficial ?

I'm really curious

Don't you find the sorting bogus ?

Let me ask you, if you were a dev and would have to create a sort function, would you make it behave like that ? Would you think it will be logical, would the customer that would ask you to do it find it logical ?

Edited December 14, 2007 by Guest

[genevieve charbon]

Could the original problem be solved with a calculation field that returns zero instead of null?

I don't want to add raw to the order table, that's obvious.

But I could do an unstored calc and with that it works ok. But I don't want to, I'm tired of creating unstored calc fields to evercome such strange behaviour.

and unstored calc vs the related field on the layout may be slower (I didn't test it).

And, while I've not tested it either, that strange behavior can affect sorted relationship (and then you can't do nothing about it)

[comment]

Don't you find the sorting bogus ?

I have already answered this question.

Let me ask you, if you were a dev and would have to create a sort function, would you make it behave like that ? Would you think it will be logical, would the customer that would ask you to do it find it logical ?

I have already answered this question too.

in what situation would you think such a strange sort order would be beneficial ?

I am not going to answer this question, because I don't want to be accused of trying to do your solution for you. I'll just say that I cannot think of a real situation similar to your example file.

I think you should wait until you understand a behavior before crying bug. That certainly wasn't the case here, and I don't know why after having it explained you insist on finding fault with it. Perhaps it's not how you would make it - that still doesn't make it "bogus".

[genevieve charbon]

I have already answered this question

You just told me that's there's a difference between empty and "doesn't exist".

That doesn't justify the end result, and since you don't know if it's on purpose or side effect, then it just means that's unexpected

There's always reason for bugs, that doesn't justify them.

Since this issue is an unexpected behaviour, that even you, wasn't anticipating before seing it (though you're glad of it for obscure reasons you don't want to share), I call this a bug like all unexpected behaviour are.

`

If you can use that feature because you can do the difference between empty and non existent good for you, but you don't seem to love it for sorting purpose

I'll just say that I cannot think of a real situation similar to your example file.

You must be kidding, the example I gave you is just that that's a real situation.

Now, if for the sake of having an expected sort, you want to build a microsftian solution where just two tables and one sort (provided not bogus) would suffice, you're right to keep it for you.

[LaRetta]

Good grief, almost every Developer on this forum has known about the difference in sorting a parent table WITHOUT children amongst those with children and so did Comment. You presume to know everyone's mind so well ... tell me, Genevieve, what am I thinking about you right now?

[comment]

No, I have told you that I think it's a very useful distinction and that it makes a lot of sense to me.

You must be kidding, the example I gave you is just that that's a real situation.

It may be real for you, but I cannot think of a reason to make it real for me. Now if you'll excuse me, I believe this subject has been exhausted.

[Vaughan]

Sounds as though *everybody* is having a tough day.

Picked a bad week to give up glue sniffing.

[comment]

Indeed, it feels like a Tombstone bar.

Can't shake those Airplane references, huh?

[LaRetta]

"Can't shake those Airplane references, huh?"

It tickles me for sure! My favorite movies on the planet; okay, I've only seen about 15 movies but nonetheless, Airplane was my favorite and I watched it 10 times!

[genevieve charbon]

Good grief, almost every Developer on this forum has known about the difference in sorting a parent table WITHOUT children amongst those with children and so did Comment.

Good for them, did they loved it ? How is it usefull ?

If it's not a bug, you'll certainly be quick to point me (and other readers that read the thread, and who are innocent) to the Filemaker documentation warning about it or praising it.

Reminds me

Good grief, everyone knows the earth is flat, that's usefull

Show me an actual good use of that undocumented, unexpected behaviour for a change.

You presume to know everyone's mind so well ... tell me, Genevieve, what am I thinking about you right now?

Well you're filling very happy to have smacked down a newbie that threatens your business of graph tidying

[LaRetta]

"Well you're filling very happy to have smacked down a newbie that threatens your business of graph tidying."

Oh, you are truly a gem. Graph tidying? Now where did I EVER EVER EVER even mention the graph? Again ... you go off in directions with no evidence; no proof, it's quite unbecoming.

If you knew ANYTHING at all about me (which obviously you don't), you'd know that I truly care about helping newbies. You can call names all you wish. Someone such as you can't possibly hurt me - only those with MINDS and HEARTS can hurt me. So yes, I'm smiling; I'm smiling because I don't have to ever listen to you again. I've added you to my personal lead wall. :clap:

[comment]

My favorite movies on the planet; okay, I've only seen about 15 movies

LOL, that IS a small planet.

[LaRetta]

Hey. My planet may be small but ... it's not the size of the planet in the fight; it's the size of the fight in the planet.

Okay, the saying is about dogs but it fits here as well. Quality outweighs quantity. :wink2:

[Vaughan]

Speaking of planets, is now the right time to talk about Uranus, LaRetta?

[LaRetta]

:bigshades: Who told you!? Yes, it IS very special but I keep it to myself. :smirk:

[comment]

I agree with the principle, although whatever sympathy I might have for Airplane, I cannot call it a quality movie.

[David Jondreau]

Well, I didn't now about this issue. And it is anomalous (bug is such a declase word) and I'm glad I know about it now.

So thanks for that (everyone).

Surely, we can now we all turn our annoyance meters down?

Edited December 14, 2007 by Guest
And don't call me Shirley!

[comment]

For crying out loud, Vaughan - we're trying to upclass the joint.

[comment]

They are only meters, David. They measure it, they don't make it.

[LaRetta]

They are only meters, David. They measure it, they don't make it.

Wiser words have never been spoken.

When you see a pot-load of good people gettin' riled, DJ, you know something is fowl in the air. That doesn't mean that a turkey can't turn things around ... I've been a turkey a time or two myself and we'all instantly forgive and forget. But suck up? Not on your life. Careful or I'll turn my planets loose on you. :giggle:

Edited December 14, 2007 by Guest
Changed plants to planets. ROFLMAO!

[David Jondreau]

They are only meters, David. They measure it, they don't make it.

Hey I understand completely. I've been following this thread (and a couple others today) with alternating horror and amusement.

Speaking of planets, is now the right time to talk about Uranus, LaRetta?

I'll ask...

So, LaRetta, are those Klingons still orbiting Uranus?

Edited December 14, 2007 by Guest
"Smokey, this is not 'Nam. This is bowling. There are rules."

[genevieve charbon]

Oh, you are truly a gem. Graph tidying? Now where did I EVER EVER EVER even mention the graph?

People who witness a crime without doing anything against it while they could are called accomplice, so they're assumed to have participated the crime.

You witnessed me bitching about the graph and being smacked down for that, so rather than expressing your graph concerns, which would have calmed down the naysayers, you did nothing.

So either you thought like they that the graph is terrific as it is, or you'e a cowards, or acting by interest.

That's just logic.

you could say that you didn't care, if so you could have bashed me, but, as intellectual honesty would suggest it, you would have told that my bitching wasn't totally pointless

you'd know that I truly care about helping newbies

I sure do, like all of you, if they don't go against your beliefs or don't insult the sacred FM Inc.

I've added you to my personal lead wall

Great for you, I've always thought those feature were for weak persons, good for them, you just show your lack of open mindness like genx when he closed the thread.

All the point of the forum, is to be a repository of knownledge. Knownledge is as much as error as in success.

In my recent threads I provided knowledge, see DJ, and the many more who will be googling in the years to come.

I told FMS will be slow no matter the hardware, I highlighted this bizarre sorting bug etc etc.

I've expressed concerns about FMP's implementation of relationship.

Thanks to TheShadow, we learn something about SQL that will prove to be interesting.

On the other hand what Soren, you, and Genx did is just bashing me. This is just noise. The googlers will see that.

So based on facts, useful one, your contribution among those thread is worthless.

[Søren Dyhr]

It seems illogical to me that no value is higher than a value

Since you seems versed in SQL, should you of all know that nothing with NULL values hardly ever gives predictable results, regardless of the tool dealing with them. Validations should be instated to prevent them at large!

All the point of the forum, is to be a repository of knownledge. Knownledge is as much as error as in success.

In my recent threads I provided knowledge, see DJ, and the many more who will be googling in the years to come.

Unfortunately are your provsions next to nothing, when sufficient cermonials gestures are lacking:

adding that their knowledge of each other was not possible without much ceremony

The real problem is:

Castalia is an Ivory Tower, an ethereal protected community within a larger nation, devoted to pure intellectual pursuits, and oblivious to the problems posed by life outside its boundaries. Knecht gradually comes to doubt whether the intellectually gifted have a right to withdraw from life's big problems. He eventually concludes that they do not, and that conclusion precipitates a sort of midlife crisis. Accordingly, he does the unthinkable: he resigns as Magister Ludi and asks to leave the order, ostensibly to become of value and service, in some way, to the larger culture.

Both quote taken from: http://en.wikipedia.org/wiki/The_Glass_Bead_Game

--sd