How to set up Text / Numeric relation

[K1200]

I have a numeric field in a Data table that normally contains a value. Since FileMaker can accept alpha characters in such a field, the field allows users to enter an error code when a value isn't available. That much is fine for most processing, but I've run upon a problem with portals.

I have a relationship defined between a text field in a Master table and the numeric field in the Data table. This relationship is solely for the purpose of allowing portals to exclude records that have a particular alpha code entered (for example, "E") when no value has been entered.

Regardless of whether I define the field in the Master to be numeric or text, the portal always shows all records. It's as if the comparison of "E" in a numeric field is never resolved / detected. On the other hand, when I place a "2" in the Master field, all Data records with a value of "2" are excluded -- so the relationship does exist. As a further test, I've even tried setting up a value list containing the "E" and setting both (Master and Data) from the same source.

Can this be made to work? Can some numeric equivalent of "E" be placed in the Master record?

Thanks for any suggestions.

(P.S. The field in the Data table must remain numeric.)

[comment]

Would it be correct to guess this happens in version 9?

[K1200]

I'm using 8.5. Are you suggesting it might work in 9.0?

[comment]

No, on the contrary. I thought you were describing a version 9 issue, but I was wrong. In version 9, you wouldn't be seeing ANY records in the portal, instead of ALL.

The problem, in a nutshell, is that text values in a number field are not indexed, unless they have a numerical component - and then only the numerical component will be considered in a relationship (e.g. if your exclude code is "EX999", it will also exclude records with the value "999").

[K1200]

Thanks for explaining that point. Although the evaluation of the relationship makes sense now, I'm still stuck with no method for a data case that seemed so clear and straightforward. (sigh)

[comment]

Why are the users required to enter anything when a value isn't available? Is there a difference between "empty" and "not available"?

[K1200]

Probably the easiest way to explain it is:

There are cases where it's worth knowing whether the measurement wasn't needed (or attempted) or whether there was an erroneous reading (marked with an "E"). The Data table doesn't currently have error indicators defined and that can't be changed at this point.

[LaRetta]

Using Windows and vs. 8.5, entering text in Master shows NO records in the portal (based upon =) and not ALL. Entering number matches properly. So I am unsure how the change to vs. 9 affects this unless I'm mistaken (quite possible).

But here is a file which shows a few options. Do you need to filter by the exact error code or would it work to always see only the errors in another portal? Well, here's a few ideas to play with. I hope it helps a bit. Otherwise, it's too bad you couldn't put a text calculation in Data which is just the code itself and then match on that instead. But I understand you can't do that.

LaRetta :wink2:

master.zip

Edited November 17, 2008 by Guest

[comment]

You cannot have your cake and eat it too. Either have the user enter the fact that there was an error into another field, or do it yourself by defining a calculation field (result is Text) =

Case ( Value = "E" ; "E" )

If you cannot add fields to the data table, you'll have to come up with a numeric error code that cannot possibly be a valid value.

[comment]

I don't get your file. I believe the requirement is to filter OUT the records marked as error:

Non-error.fp7.zip

[LaRetta]

If so, the = will work. It really isn't very clear; the join criteria was never specified nor was it specified whether the codes were included in the match. If :notequal: was used then that would explain why ALL records were shown. I posted mostly because of the difference in my results regarding 8.5 vs. 9.

[K1200]

This relationship is solely for the purpose of allowing portals to exclude records that have a particular alpha code entered (for example, "E")

Yes.

[comment]

Ah, I see now - it's the 'Valid Codes' TO that's relevant here. But the problem is that it's filtering out empty values as well.

But you gave me another idea...

Non-errorCF.fp7.zip

[LaRetta]

Now THAT is beautiful. :waytogo:

[K1200]

comment, thanks for an innovative solution. It achieves what I was hoping for. But can I trouble you for two clarifications?

First, cValidDataIDs is a text field -- shouldn't it be numeric like the Child::Value field? It works as text, but I'm just curious.

Second, although I follow (mostly) your custom ListIf function, how can I change it to include the letter "R" in the list of valid (acceptable) values? I tried inserting "R"&Case... etc., but got nowhere.

I know this is pushing the envelope, but I've thought of another use that would require excluding "E", but allowing "R" codes. It seems it might be possible.

In any event, thanks again for the solution.

EDIT:

OOPS ... I had overlooked a major point -- that the relationship is based on IDs. I'll have to look at this a little more.

[comment]

1. The function returns a list of values, and a list of values is always text.

2. The file I have posted excludes "E" values only. However, you might need to refresh the window after modifying a value in order to see the modification reflected in the other portal - that is often the case with relationships that depend on the results of other relationships.