This one's complicated 1

[Tearose]

In our database, there are a couple fields where we use an abbreviation. Now a user wants to have a printing layout in which the full word appears instead of the abbr. We have a file for each of these fields with the abbr and the full words in tab-delimited form. Is there some way with related databases and/or scripts, that I can make this layout?
TIA
Jill

 

[JeanW]

Hi Jill,

After years I have now a file/table with all the possible abbrevs with the equiv full lenght. Sooner or later somebody will request it....

You could hardcode the full lenght of the abbrevs and use the substitute function in a calc field.
Put this field on the print layout.

This is not very clear to me:
>>We have a file for each of these fields with the abbr and the full words in tab-delimited form.<<

Do you mean a record instead of a file ?

If it is a file, you could use an open relationship between the files and use a lookup to pull the value in a field.
This is harder to do in an existing app. You need to relookup all the records. Hence the calc field.

Or make a relationship between the abbrev fields in the files and put the related field with the full name on the print layout.

HTH

JW

 

[Tearose]

Probably the last thing you wrote is what I need, but you'll have to tell me how to do it. A lookup may also work, but I've never done one, so you'd have to teach me that, too.

I'll try to make things a little clearer for you. Our database (the same one you've helped me with several times before) is for a major public rose garden. The particular fields in this case are for class and color. The print layout will be for printing directly to a plastic sign engraving machine, to make the labels used in the garden.
In the database we use (for example) G for Gallica roses, F for floribundas, HT for hybrid teas, and ab for apricot blend, mp for medium pink, etc. They're the abbr. used by the American Rose Society. The signs need to say Gallica, apricot blend, etc.

The tab delimited files, I presume, are in something like notepad so they can be imported to another program. Hope that's all clear, now.

 

[JeanW]

Make a FM file with the 2 fields in it, the full name and the abbrev name. (I think you're still in FM 6, if not let me know). Make for each abbrev a record. This will have the advantage if there is a new sort, you just have to add a record.
You could import the names in this file, just try it out.

Make a relationship Abbrev, between the abbrev field in the main file and the abbrev field in the Abbrev file.

In the print layout, put the nameFlower field, based upon the relationship, on the layout.
It will look something like this, ::nameFlower.

Now let's go for a lookup.
First de difference between a relationship and a lookup, when to use them.
For data manipulation you can use a relationship or/and a lookup. Both methods of data exchange are useful for a variety (looks like flowers) of database actions.
It's like an Alias on a Mac or a shortcut in Windows.
These 2 features give you a pointer to an actual file or application on your pc. The advantage is being able to have multiple pointers to one file without duplicating the file or application. F.e. Word may be located in your Programs folder, but you also want to be able to launch it directly from an icon on your desktop.

The same is true for data in a FM file.
You may need to access information from several different databases, but want to store it in a single file or table.
In this case, a relationship would work exactly like a shortcut or alias, it let's you see data stored in external files.
In contrast, a lookup does NOT point, but rather DUPLICATES your data.

Because you can avoid duplicating data, it would seem that a relationship is always better than a lookup. The truth is, it depends on the situation.
If you just want to display the most current data in different places, you would use a relationship. A relationship allows you to make one modification and have it reflected wherever that information appears ( ::yourFieldName).

On the other hand, a lookup makes a COPY that reflects the state of that information "at the time it's being used".
For example, lookups would be better used between a product and invoice database because you would want to maintain, on the original invoice, the price at which a product was ordered.
In contrast, a common use of a relationship is between a customer information and an invoice file. You always want to keep the most current phone number for the customer, so you use a relationship to ensure that changes in one place are reflected in every other.

Now you know, a relationship reflect the data and a lookup copies the data.

How to make a lookup.
You need a relationship for this. Like the relationship I mentioned above.
Now you make a field in the main file, fullFlowerName.
In the options for that field, in the Autoenter part, you will see looked-up value -- Specify.
This will bring you in a second screen.
Lookup a value from the first matching record given by the relationship....and the name of the relationship will probably be 'unknown'.
Click on the down arrow and a list of the relationships will be given. Make your choice.
The possible fields will turn from grey to black and there will be a text, showing on which fields in the current file and which field in the related file the relationship is based.
Make a choice for the needed field.
At the right side you will have radiobuttons 'if no exact match'.
Here you can use the last option, use, and fill in something that will be used as data if there is no related value, like 'No name'. This will allert you that the used abbrev is not in the file.....

Put the field on a layout, fill in the abbrev and the right full name value will show in the field.
For a lookup you need a trigger field. Here the abbrev value in the main file will trigger the lookup. As long as you don't change this value, the full name will remain.

If you have 100+ records you have to copy paste the abbrev value in each record or make a script with a loop to relookup this value in each record.

If problems, feel free.

JW

 

[Tearose]

IT WORKED!! It took till yesterday for me to have time to try it, but with some effort, I got it to work.Thanks for all your help!
Jill