Substitute

[hall312]

I have several codes (example AA, AA1) that are substituted into their full descriptions upon records import.

Example AA - Aggravated Assault

Everythings great except for the codes with numbers. This is what I want

AA1 - Attempted Aggravated Assault

This is what I get

AA1 - Aggravated Assault1

Whats the trick with the letter number combo?

[LaRetta]

Whats the trick with the letter number combo?

You need to create a table to hold this translation, such as:

Code - Description

AA.....Aggravated Assault

AA1....Attempted Aggravated Assault

etc

You would relate the tables based upon the Code. Then just place the description from your Translated table directly on your layout next to the code; no need to include the description into the main table at all. This also allows for addition of new codes and changes of descriptions (from Browse mode in the translation table) instead of requiring modification of script or calculation. :smile2:

[hall312]

LaRetta, you're a glutton for punishment answering my questions , but I sure am glad you are!

When I receive my weekly cases report it is an excel spreadsheet. One of the columns on the spreadsheet is case_code. And it will for example say AA. When I import this weekly report into FileMaker I don't want to see AA in my case code field - I just want to see Aggravated Assault. And often there are multiple charges per case. I was using Substitute with 98% success except for the number letter combos.

Your way intrigues me, but loses me as well. Could you show me an example?

[comment]

The order of substitutions is important:

Substitute ( "AA1" ;

[ "AA" ; "Aggravated Assault" ] ;

[ "AA1" ; "Attempted Aggravated Assault" ]

)

returns "Aggravated Assault1" because the pattern "AA" is substituted first.

However,

Substitute ( "AA1" ;

[ "AA1" ; "Attempted Aggravated Assault" ] ;

[ "AA" ; "Aggravated Assault" ]

)

finds the pattern "AA1" BEFORE it looks for "AA", and therefore returns "Attempted Aggravated Assault".

That said, I agree with LaRetta that you should leave the codes as imported, and use a relationship to another table in order to decode them.

[LaRetta]

Here's a file showing the idea of using a table. You would import the code into your Code_Report table but needn't show it anywhere on your code forms. You would just show the related code description instead.

If the wording happens to change on a code, simply change the wording in the Code's description and it will change on all your code report records. :smile2:

Case_Code.zip

[hall312]

Very nice! What about several codes in one excel field -

AA,BT,TT - always separated by commas and sometimes by a space after the comma.

Is there a way to put them all in one field in the database upon import?

[Lee Smith]

Did you try entering your codes and Translations on the "Codes" Layout?

[hall312]

Yes. Works fine except for the multiple codes, it shows blank.

[comment]

See if this helps:

Cases.fp7.zip

[hall312]

Sure does! Thanks comment... your code descriptions are great. My goal by the end of the month is to arrest someone on a Tiny Tim charge! Thanks for the help and the joke.