displaying text/data from another file, but storing it as an ID #

[beverson]

Okay, I'm pretty new at this, so this probably isn't terribly difficult I just can't get it to work. I have three files -- a main file containing records of sources for news stories, a second file containing a list of states, and a third file containing a list of "areas of expertise." The states and areas of expertise files are simply lists of the obvious with reference ID #s assigned to them (Alababma is 1, Alaska is 2, etc.). The state file also has postal abbreviations. I have two separate but similar problems:

-- I want to be able to specify multiple areas of expertise for each source, but I want to store that information simply as the ref # assigned to each area in the related file. I have been able to get the main file to read and display the value list from the related "areas..." file as checkboxes on the layout, but then it tries to store the actual text value ("Aging," "Affirmative Action") instead of the ref. number (1, 2). I need it to store the number instead because we will eventually be exporting this data to a SQL server for use online and I need simpler data (ref #s) in the Areas of Expertise field instead of just putting the text in there.

-- I would like to display the state as the full name of the state, but store it as either the postal abbreviation or the ref #...since each state field will only have one state in it as opposed to multiple areas of expertise like above, using the abbreviation will be fine. Again, I can get the pull-down menu on the layout to show the full name of the state (or the abbreviation for that matter), but I can't get the main data file to store the alternate format for the selected value.

Any help would be GREATLY appreciated...I'm pulling my hair out here, partially because this seems like it should be simple but I just can't figure it out. Thanks in advance!

[zincb]

The second part of your question is easiest:

To store the StateRefNum in the Sources file, create a numeric lookup field, based on a relationship between Sources:StateName and States:StateName. This new field should lookup the StateRefNum field in the States file.

If you don't truly need to store StateRefNum in the Sources file and only want to export it to your SQL server, don't add the StateRefNum field to your Sources file, but do create the relationship between Sources:StateName and States:StateName. When exporting, simply add the States:StateRefNum field to your export list, via this relationship.

Now for the first part of your question:

The expertise list produced by the checkboxes is essentially a text file and, as such, it can't easily "trigger" anything. To convert each entry to it's corresponding experience reference number and to store these values in *text* field, ExpRefNum, a script can be used.

The script is very simple, but repetitive. If you later add a new expertise category, it will have to be added to the script.

Enter Find Mode

Set ( Expertise = "agin" ) ** no need to search for the entire expertise

Perform Find

Loop

Set ( ExpRefNum = "1 , " & ExpRefNum ) ** "1" is the reference number for "aging" and the comma separator between can also be changed to a paragraph mark, a pipe, etc...

Go to Next Record (exit after last)

End Loop

# ** a blank comment to visually separate each expertise

Enter Find Mode

Set ( Expertise = "affirm" ) Perform Find

Loop

Set ( ExpRefNum = "2 , " & ExpRefNum )

Go to Next Record (exit after last)

End Loop

#

Keep going until you have all your expertises and their associated reference numbers loaded into the script.

After you run the script, your Source:ExpRefNum will look like "1, 2, 6, ..."

For SQL purposes, it may be best to separate with whitespace, such as spaces or the Paragraph mark. In the latter case, you output would be:

1

2

6

etc..

[zincb]

Oops! Forgot to initialize ExpRefNum!

Add to the top of the Script:

Show All Records

Replace All ( no dialog , ExpRefNum , "" )

If you leave this off, the horizontal list will append itself to the last time you ran the script.