Automatically Filled In Values

[TechSpec]

I've inherited a database that uses an external file that is linked in a relationship to fields in the main database. There are 5 fields, A-E. When I enter a value in the field A, the other 4 fields are automatically filled in with values from the external file, based on a lookup of the value entered in field A. How is that done? I see no script or button which says "automatically add the values to the other 4 fields?"

If this is the wrong forum, could you point me to the correct one? Thanks.

[mz123]

If you look at the definitions of the automatically-filled fields (File > Define Database) you'll see that they are either auto-calculations with related information or look-ups.

Martha

[TechSpec]

I don't have File->Define Database. Under File I have Define Fields, Define Value Lists, and Define Relationships. In my first post I used the field names A-E. Here are the real names:

A=NumberRight

B=:ScaledScore

C=::NationalPercentile

D=::Stanine

E=::GradeEquivalent

Those double colons are part of the field names. In my external file, the field names are:

NumberRaw

ScaledScore

NationalPercentile

Stanine

GradeEquivalent

And in the main database there is relationship linking NumberRight and NumberRaw under the relationship name RawScore:

NumberRight=::NumberRaw

and the related file with the table data is specified.

What I still don't understand is what the mechanism is that automatically fills in the last four fields when I enter data in the first field. In the main database, there are no specific field definitions for the last four fields, and they are not listed in the relationship definition either. The NumberRight field which is related to the NumberRaw field is a field of type text and options set to Indexed. Is it that indexing that somehow fills in the other fields? How does the database know to fill in just those four fields?

[Lee Smith]

Hi Tech,

Martha already explained where to look, except she didn't tell you need to click on the "Options" button once you are in the "Define Fields".

There are three tabs, Auto Enter, Validation, and Storage.

HTH

Lee

[mr_vodka]

I don't have File->Define Database. Under File I have Define Fields, Define Value Lists, and Define Relationships. In my first post I used the field names A-E. Here are the real names:

A=NumberRight

B=:ScaledScore

C=::NationalPercentile

D=::Stanine

E=::GradeEquivalent

Those double colons are part of the field names.

I would strong recommend not naming your fields with double colons.

[Lee Smith]

Hi John,

It sounds like those are related files by his description.

Lee

[mr_vodka]

You are probably right Lee and I took it as that too. But since people may misread or rather may get confused by the statement "Those double colons are part of the field names", I thought it would be wise to post a warning anyway.

:)

[Lee Smith]

Hi John,

Excellent points. But, it is posted in the Relationships Topic, and they would have got that Warning about using : double colons as part of a Field Name, and the field not being easily used in a calculation..

Lee

[TechSpec]

Hi Lee and John,

As I said up top, I inherited this database, so the field names were already set when I came along. Plus I suspect that the double colons have a specific meaning to field names - probably having to do with relationships.

As Martha pointed out (thank you) I looked at the field definitions, and I did look at the options, and there was nothing checked on the first two tabs - Auto Enter and Validation - and on the third tab only Indexing is checked "On". I've gone through every field in both files and cannot see where or how fields 2 through 4 are auto-filled.

[Lee Smith]

Hi Tech,

I feel your pain, it is not easy to work on someone else's creation, especially if they didn't leave good notes.

To help you understand the files a little better, a relationship has to parts. They are referred to in many ways, such as, Master and Slave, Current and Related, PK and FK (Primary Key and Foregn Key), and problably others. I will use the terms I learnd by when I first started developing, Current and Related.

The Current file is the one that you are viewing the data in, and the related one is the where the data is maintained. In your case, the one where the fileds have the double colon is the Current File, and the one that doesn't is the Related File.

When tracing where and how information is being gathered, you need to examine the related File for the auto enters, calculations, etc..

HTH

Lee

Edited April 16, 2007 by Guest

[TechSpec]

Hi Lee,

Thanks for the terminology. That will help.

Question: shouldn't I see at least one checkmark on the Auto Enter tab for the options? There's no checkmarks there.

[comment]

Everyone is assuming your field is defined as a lookup, because that's what you said in your original post. I am guessing it might be simply a calculation field, tunneling the data from the other file.

Why don't you post a screen shot of the field definitions?

[TechSpec]

Everyone is assuming your field is defined as a lookup, because that's what you said in your original post. I am guessing it might be simply a calculation field, tunneling the data from the other file.

Sorry about that. That was before I knew lookup was a defined method. I was only referring to the fact that the auto-filled-in fields were getting values that were looked-up in a table, not that they were defined as an FMPro "lookup."

Please lower my Intermediate status to Beginner.

I have figured it out, though, by creating a new database with sample fields and a sample data file.

It seems that the fields with the double colons in the Current file, the aforementioned :ScaledScore, etc., are actually fields from the Related file. When I created this new database and set up a relationship between NumberRight and NumberRaw, I then went to Insert->Field, and in the dropdown chose Define relationships..., then was able to insert the actual fields from the Related file - their names had the double colons in front.

So I guess that's why there was nothing checked on the Auto Entry tab for those fields. They're not auto-filled-in with data from the Related file, they are the actual fields from that file.

Does that sound right?

Thanks to all for their help.

[comment]

Not entirely. First, related fields are referenced as RelationshipName::FieldName. If you don't see the RelationshipName part, then something is probably wrong.

More importantly, you can't see the actual fields from another file in the Define Fields window. You can REFER to them in calculations, but they are DEFINED in their own file.

[TechSpec]

Here are the steps for creating my sample files.

The key to my understanding the databases I inherited is realizing that the databases comprise a relational database, not a lookup.

1. File->New Database..., and name it main

2. Define one new field, A of type text

3. File->New Database..., and name it data

4. Define three new fields, "DA", "DB", "DC" of type text

5. Create three records in database data and fill in these values:

DA DB DC

1 red apple

2 yellow banana

3 tan cantaloupe

6. File->Define Relationships..., click on new and choose the data database file

7. name the relationship fruits

8. click on the A field on the left (main) database

9. click on the :DA field on the right (data) database

10. click okay, then done

11. return to the main database, go into layout mode

12. Insert->Field

13. on the dropdown on the top, select "Define Relationships..."

14. select fruits and done

Now you see the three fields from the data database with the double colons in front

15. select ::DB and then okay

16. Insert->Field

17. select ::DC and then okay

18. go into browse mode and enter the value 1 into field A

You can see that fields ::DB and ::DC display their values as entered in database data, and that if you go into File->Define Fields... those two fields are not listed, as comment said.

Furthermore, if you go to the data database and for record 1 change the value "red" to "green" for field DB, and then go back to database main, you'll see that the change has occurred there as well.

Going back into layout mode for database main, the fields are named A, ::DB, and ::DC. There's no reference to fruits::DB or such. It probably would have been clearer had FileMaker prepended either the relationship name or the database name.

This is true of FMPro V5. Your mileage may vary.

Thanks to all who have replied.

[comment]

True, when you are in LAYOUT mode (and if you do NOT select 'Show > Sample Data'), the related fields placed on the layout will display "::fieldname". That is an entirely meaningless cosmetic.

When you refer to a related field, in a calculation formula or in a script step, the reference is (and must be) in the form of:

RelationshipName::FieldName

Now, do you still have a question that needs to be answered?

[TechSpec]

Now, do you still have a question that needs to be answered?

No, that was it. Thanks.