Relationship from text field to number field

December 7, 200124 yr

For a solution I'm using a many to many relationship by using a 1 global (text) field with several keyID's on 1 side and a file with many related records on the other side. I'm actually using FM's nice feature of indexing every 'word' to create a multiple key with just 1 field.

The gKey on the master side has multiple entries separated by the paragraph symbol. On the child side of the relationship is a normal number key field. Each user can collect his/her own set of ID's in the gKey field after which a script can be run to update all child records in one go.

The question:

- In FM4.1 this relationship works fine but would be have a potential problem when upgrading to FM5(.5)?

- Is there a better approach for this? is it possible to store multiple numbers in 1 number field?

Many thanks

December 8, 200124 yr

You should not run into any problems when updating. However I do not like relating text to number fields. There are some design problems that are possible in this scenario that can cause it to not work and it is often difficult to figure out the reason.

There is NOTHING that you cannot do with a text field that you can do with a number field. Unless you are doing mathmatical calculations on that number field, I suggest making them both text. If you are doing math calculation you can use the TextToNum () function to do those calcs.

December 10, 200124 yr

Author

Thanks Kurt,

Unfortunately I can't really change the number key on the right side of the relationship, it would involve too many changes to other relationships involving the same field. I didn't like the idea of linking text to number myself thus the post to check whether others have run into trouble doing so. I'll avoid it wherever possible.

You wrote about possible design problems, what should I look out for?

Is there a limitation to the number of multiple key entries in 1 field; will FM stop indexing a field if its contents exceed a certain limit?

If I want to collect only 1 to max. 10 numbers, would you suggest another solution then the global text field?

Thanks for your time and help.

Andries