text field vs numeric field in sorting issue

[balooka]

Hi

I have a table with a 'lesson-ID' that looks like 1.1, 1.2, 3.2 etc, these are subcategories of field 'type'.

Each ID also jas a name to it. For instance 1.1 has is named ONE and 1.2 is named TWO.

I have an exams table that looks up values from the ID table using a global (type A, B or C) plus the subcat (1.1, 1.2 etc).

I  need to look up values from the ID table using the type and ID ->    A 1.1   /   A 1.2

The fields are of the same kind in both tables, type is test and ID is number.

 

My issue is that when I do a match it sees the '1.1' as '11'.  It will match up to both 1.1 AND 11 it looks like because it refuses to show me the name (there is more than one match!).

If the ID is 3.13 I have no errors since there is no ID 313 and therefore there is no double record.

I can fix this by change the fields to text fields, then it all works just fine, however, my sorting goes bananas because it now sorts 3.10 right after 3.1

 

Hope that's clear? How can I get it to match 1.1 as 1.1 and not 11? I'm not sure where to look (took me an hour to find the source of the problem!).

Thanks!!

[comment]

34 minutes ago, balooka said:

My issue is that when I do a match it sees the '1.1' as '11'.  

I think that is the only relevant thing here, is it not? And the only possible explanation I can think of is that your file is not using a dot as the decimal separator.

 

[balooka]

That could very wel be it... but I have found another way to tackle this. I added a calc field with numeric result and as a calc I used the textfield. The value of that calc is 1.1 where is was 1.1 in the textfield... odd.

Anyway, I can use that to sort my records again.

[comment]

43 minutes ago, balooka said:

The value of that calc is 1.1

I am afraid that doesn't add up. In your place, I would try to understand the cause of the problem, because it might affect other things too.

 

[balooka]

Thanks Comment,

 

But I have no clue left where to find it to be honest. Somehow if it's a textfield it does recognize '1.1' correctly. if I use it in number fields it does not.

If I calculate the contents of a textfield into a number field the result is 1.1 again. I'm confused about why it recognizes the 1.1 to be a valid outcome in a calc field and not when I use a number field.

 

[comment]

3 minutes ago, balooka said:

I have no clue left where to find it

See if you can reproduce the problem in a new file. You only need two tables with a number field in each. If you see the same behavior, post the file here.  If not, make a copy of your file, remove everything except the two tables joined by the relationship and their matchfields, and post it here. To stress the point: we want to see a file where 1.1 matches 11.

 

[balooka]

OK, I have made a file with two tables, A and B.

In table A I want to insert an Edition (I only made the edition 'A' so anything else will not match).

In table A I also add the number I need and what this is all about. There are two fields in table A where you should put the same value (like 1.1 or 11 or whatever).

The A_NUM field is numeric, the A_NUM_T is a textfield. I have made two relations, one for each.

The result on the Table_A layout should be the name of the related file. You can view both results in the Table_A layout.

The values in Table_B are unique, no duplicates.

 

 

match.fmp12

[comment]

When I open your file on my system, I see the actual value in A_NUM field as 1,1 not 1.1. Similarly, in B_NUM field in record #14 of Table_B I see the value of 1,1. A string containing a comma is not a number, unless your file is set to use comma as the decimal separator. Your file is set to always use the current system's setting - so the question is what is your OS using as the decimal separator.

Added: I see that the file's settings are to use a comma as the decimal separator. I believe you have entered "1.1" into a number field on a system that requires a comma to be used as the decimal separator, and therefore the value is indexed as 11 ( same as any string value would be indexed using only digits, the minus sign and the decimal separator - see the attached demo).

 

NumIndex.fmp12

Edited June 21, 2017 by comment

[balooka]

I'm not using the systems settings as far as I know - but the separator is a comma. Is this the cause of all this?

[comment]

You are using the system setting (look under File Options > Text). Formatting the display of the field is irrelevant here. The problem is that your field contains a non-numeric value (see the addition to my previous post).

[balooka]

Ouch! I was looking at the wrong place for that setting then. I assumed that field formatting overruled any and all preference.

Just as a side question... If you make a new calc field where the formula is 'A_NUM_T' and the output is text, it shows the original value again. Does that mean that the value as entered remains the same, even if the numeric field is not displaying it? 

[comment]

50 minutes ago, balooka said:

I assumed that field formatting overruled any and all preference.

No, it only affects the way the field is displayed on the layout. Keep in mind that you can add another instance of the same field to the layout and format it differently. 

 

50 minutes ago, balooka said:

If you make a new calc field where the formula is 'A_NUM_T' and the output is text, it shows the original value again.

Filemaker keeps the original value as entered. If you enter "7x01" into a number field, you will get "7x01" as the result of GetAsText ( Numberfield ). You will also see the original value when you click into the number field - no matter how it's formatted. But the value will be indexed as "701".

 

Edited June 22, 2017 by comment