Hi all,
I'm trying to connect to a FM Pro database via ODBC. I'm working with
perl, everything is allright, I can connect and retreive records but
in my database I have fields that are multivalued (?). For these
fields, only the first value is returned via ODBC. Is there a trick to
get all values or is it an ODBC bug ?
Thanks for your help.
syma
In article
<20dc8da3-55a1-4ea5-961c-5375534b0343@b1g2000pra.googlegroups.com>,
syma2007@gmail.com wrote:
> Hi all,
> I'm trying to connect to a FM Pro database via ODBC. I'm working with
> perl, everything is allright, I can connect and retreive records but
> in my database I have fields that are multivalued (?). For these
> fields, only the first value is returned via ODBC. Is there a trick to
> get all values or is it an ODBC bug ?
>
> Thanks for your help.
>
> syma
"Multivalued"??
If you mean a field that is formatted as Checkboxes, then the data in
these fields is separated by a Return character, which may be causing
the problem - return characters usually denote the end of the record.
You can get around this using a new Calculation field in FileMaker to
swap the Return characters to commas (or whatever else you want to
use).
eg.
MyField_ODBC Calculation, Text Result, Unstored
= Substitute(MyField, "*RET*, ", ")
where "*RET* is the 'backwards P' Return character that is on one of
the buttons in the Define Calculation window. Then use this field
instead of the original for your ODBC needs.
If you mean Repeating fields, then do not use them. Repeating fields
are just a pain in the sit-upon in almost all cases that people THINK
they should be using them. Instead use separate fields.
eg.
MyField_1
MyField_2
MyField_3
etc.
If you insist on keeping the Repeating field (which you'll likely
regret later), then the workaround would be to use the GetRepetition
function to extract the repeatitions' data.
eg.
MyField_ODBC_1 Calculation, Text Result, Unstored
= GetRepetition(MyField, 1)
MyField_ODBC_2 Calculation, Text Result, Unstored
= GetRepetition(MyField, 2)
MyField_ODBC_3 Calculation, Text Result, Unstored
= GetRepetition(MyField, 3)
etc.
You can of course have one Calculation that joins all these values
together.
ie.
MyField_ODBC Calculation, Text Result, Unstored
= GetRepetition(MyField, 1) & ", "
& GetRepetition(MyField, 2) & ", "
& GetRepetition(MyField, 3) & ", "
etc.
Again, use this field instead of the original for your ODBC needs.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
Yes, I meant repeated fields, error in translation from french,
sorry ! In fact I'm trying to get datas from a filemaker base which
has been designed years ago, I can't change the structure. So I think
I'm going to try with the getRepetition function. The fields are
repeated up to 50 times, I think it's going to be a great pleasure !
THanks for your help
syma
On 20 d=E9c, 21:15, Helpful Harry
wrote:
> In article
> <20dc8da3-55a1-4ea5-961c-5375534b0...@b1g2000pra.googlegroups.com>,
>
> syma2...@gmail.com wrote:
> > Hi all,
> > I'm trying to connect to a FM Pro database via ODBC. I'm working with
> > perl, everything is allright, I can connect and retreive records but
> > in my database I have fields that are multivalued (?). For these
> > fields, only the first value is returned via ODBC. Is there a trick to
> > get all values or is it an ODBC bug ?
>
> > Thanks for your help.
>
> > syma
>
> "Multivalued"??
>
> If you mean a field that is formatted as Checkboxes, then the data in
> these fields is separated by a Return character, which may be causing
> the problem - return characters usually denote the end of the record.
>
> You can get around this using a new Calculation field in FileMaker to
> swap the Return characters to commas (or whatever else you want to
> use).
> eg.
> =A0 =A0 =A0 =A0MyField_ODBC =A0 =A0 =A0 =A0Calculation, Text Result, Unsto=
red
> =A0 =A0 =A0 =A0 =A0 =A0 =3D Substitute(MyField, "*RET*, ", ")
>
> where "*RET* is the 'backwards P' Return character that is on one of
> the buttons in the Define Calculation window. Then use this field
> instead of the original for your ODBC needs.
>
> If you mean Repeating fields, then do not use them. Repeating fields
> are just a pain in the sit-upon in almost all cases that people THINK
> they should be using them. Instead use separate fields.
> eg.
> =A0 =A0 =A0 =A0MyField_1
> =A0 =A0 =A0 =A0MyField_2
> =A0 =A0 =A0 =A0MyField_3
> =A0 =A0 =A0 etc.
>
> If you insist on keeping the Repeating field (which you'll likely
> regret later), then the workaround would be to use the GetRepetition
> function to extract the repeatitions' data.
> eg.
> =A0 =A0 =A0 =A0MyField_ODBC_1 =A0 =A0 =A0 =A0Calculation, Text Result, Uns=
tored
> =A0 =A0 =A0 =A0 =A0 =A0 =3D GetRepetition(MyField, 1)
>
> =A0 =A0 =A0 =A0MyField_ODBC_2 =A0 =A0 =A0 =A0Calculation, Text Result, Uns=
tored
> =A0 =A0 =A0 =A0 =A0 =A0 =3D GetRepetition(MyField, 2)
>
> =A0 =A0 =A0 =A0MyField_ODBC_3 =A0 =A0 =A0 =A0Calculation, Text Result, Uns=
tored
> =A0 =A0 =A0 =A0 =A0 =A0 =3D GetRepetition(MyField, 3)
>
> =A0 =A0 etc.
>
> You can of course have one Calculation that joins all these values
> together.
> ie.
> =A0 =A0 =A0 =A0MyField_ODBC =A0 =A0 =A0 =A0Calculation, Text Result, Unsto=
red
> =A0 =A0 =A0 =A0 =A0 =A0 =3D GetRepetition(MyField, 1) & ", "
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 & GetRepetition(MyField, 2) & ", "
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 & GetRepetition(MyField, 3) & ", "
> =A0 =A0 =A0 =A0 =A0 etc.
>
> Again, use this field instead of the original for your ODBC needs.
>
> Helpful Harry =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> Hopefully helping harassed humans happily handle handiwork hardships =A0;o=
)