I have two tables that I have no one field to relate to between them.
To relate them I really have to use a combination of four fields. What
I did was created a Calculation field and just concatenated the four
fields as one text field.

The problem is one of the four fields is a number field in both tables.
The values in the field are 0.123 (always under 1). In Table A the
concatenated linker field pulls in 0.123, but in the calc field in the
other table pulls in .123, thus I never get the table to properly
relate.

Any ideas why one uses 0.123 while the other uses .123 when building
the text value in the calc field? When looking at the data both
display the number field as 0.123.

Doug Anderson wrote:
> I have two tables that I have no one field to relate to between them.
> To relate them I really have to use a combination of four fields. What
> I did was created a Calculation field and just concatenated the four
> fields as one text field.
>
> The problem is one of the four fields is a number field in both tables.
> The values in the field are 0.123 (always under 1). In Table A the
> concatenated linker field pulls in 0.123, but in the calc field in the
> other table pulls in .123, thus I never get the table to properly relate.
>
> Any ideas why one uses 0.123 while the other uses .123 when building the
> text value in the calc field? When looking at the data both display the
> number field as 0.123.
>

Hi Doug,


Text (text field) = AAA
N (number filed) = 0.123

calcF = TEXT & " " & N

returns AAA 0.123 , when calF calculation result is set as text or number

You can use:
TEXT & " " & GetAsText ( N)

or:
"0." & Right( N; 3)

for massaging this sort of thing too.



If you are using FM8, you cant just use a multi-parameter rel definition?


regards

Chris

Doug Anderson wrote:
> I have two tables that I have no one field to relate to between them.
> To relate them I really have to use a combination of four fields. What
> I did was created a Calculation field and just concatenated the four
> fields as one text field.
>
> The problem is one of the four fields is a number field in both tables.
> The values in the field are 0.123 (always under 1). In Table A the
> concatenated linker field pulls in 0.123, but in the calc field in the
> other table pulls in .123, thus I never get the table to properly relate.
>
> Any ideas why one uses 0.123 while the other uses .123 when building the
> text value in the calc field? When looking at the data both display the
> number field as 0.123.
>

check that the field calculated results/ field types are the same

Text AAA
N = 0.123


calc1 = TEXT & " " & N
result: AAA 0.123

calc2 = TEXT & " " & GetAsText ( N)
result: AAA 0.123

calc3= TEXT & " 0." & Right( N; 3)
result: AAA 0.123


If you are using FM8 (? 7; so long I forget), you can use a
multiparameter rel definition using the 4 fields


regards

Chris

In article <2007071116141216807-dougcpa [at] maccom>, Doug Anderson
<dougcpa [at] mac.com> wrote:

> I have two tables that I have no one field to relate to between them.
> To relate them I really have to use a combination of four fields. What
> I did was created a Calculation field and just concatenated the four
> fields as one text field.
>
> The problem is one of the four fields is a number field in both tables.
> The values in the field are 0.123 (always under 1). In Table A the
> concatenated linker field pulls in 0.123, but in the calc field in the
> other table pulls in .123, thus I never get the table to properly
> relate.
>
> Any ideas why one uses 0.123 while the other uses .123 when building
> the text value in the calc field? When looking at the data both
> display the number field as 0.123.

The Number field may DISPLAY as 0.123, but that's not necessarily how
it is stored. Number fields (and numbers in Text objects) can be
formatted to display in virtually unnlimited ways, but they are all
stored in the same internal format.

When you concatenate a Number field to some text (with either a Number
or Text result), FileMaker discards the leading zero, as well as any
other formatting the number field has - it's irrelevant to FileMaker
since the format can be different on every Layout.

To fix your problem, since the number is "always under 1", you can
simply concatenate the 0 to the front of the number in the Calculation.
ie.
TextCalc Calculation, Text Result
= Field1 & Field2 & Field 3 & "0" & NumField



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)