I know there has to be a way to do this but I can;t figure it out!

I run a very large embroidery shop. recently we took on a new customer that requires us to cross reference our thread with theirs. What I am trying to do is figure out a way that when I type in "their" thread number into a text field (or number field, whatever)our thread number pops up in a different field, that way I get automatic cross referencing.

The problem is I can;t figure out how to do this! Any help would be HUGELY appreciated.

Please, this is very important for me to figure out.

thanks,

Steve

If you're only talking about 1 instance you could use a "hard-coded" method. Include it in Define Fields for Field2, Options, Auto-enter, then choose Calculation: Case (field1 = their thread#, your thread#)

Field2 will then fill in with your thread# whenever you first enter their thread# in Field1. It will not, however, change if you edit Field1. It will not work if they change the thread# later; you'd have to go in and adjust the calculation; not that hard. (Their thread# being the actual number/text.)

Needless to say, this is not a good way to do it if you have a lot of these kind of cross references. I just realized this is probably what you're talking about, several different thread matches. In that case you'd want a separate little file, with the fields their thread# and your thread#.

In the main file you'd create a relationship from their thread# to the little file's their thread#. Then, in the main file, for Field2 in Define Fields, Options, Auto-Enter, Lookup, choose the above relationship, then YourThread field as the field to copy.

That way you could have a whole series of these cross-references, easy to see and manage.

Steve:

I suppose the answer depends on how many thread numbers there will be, but your best option is to create a database of thread numbers called, say ThreadNumbers.fp5. It need only have three fieds - SerialNumber, YourNumber, TheirNumber. Then, in your current system, you would set up a relationship to this database, based on YourNumber. Put a related field, TheirNumber, into your current layouts, and when you type in YourNumber, the TheirNumber field should automatically be populated with the correct related number.

Of course, you've got to sit there and enter all this data into ThreadNumbers.fp5, but once it has been done, you'd be all set.

-Stanley

Thank you Stanley and Fenton,

It seems like you both are suggesting a similar approach that I thought I had tried but apparently incorrectly. I was using lookup fields but I could not get the number to display the "replacemnt" number in the second field. I know I ma missing something very basic here and it's frustrating because I should know better.

Anyway, Stanley, when you say three fields only, are there then records created for each individual thread number? In my case 83, or is there a field for each individual thread number. I also do not see the need for a seriel number field in the small "threadnumbers.fp5" database. What function does the serial number do?

Thanks for your help, I will go back to the drawing boards to see if zi can figure this out!

Steve

I figured it out...You guys ROCK...Thank you, thank you, thank you!

Steve

PS now I gotta go do all the grunt work, solving the puzzle is always the fun part!

Steve:

Glad you could figure it out. The serial number should always be there, because if at some point you end up with two entries that look identical, but aren't, you can tell the difference... Really, it's just good practice.

-Stanley