Auto Fill Feilds

[waymon]

So I have 1 Table that has many different fields.

I'm looking for a way so that when I type into 1 field, they auto fill other fields when the data matches all records to the original field.

So I have Field called: Job Number.

Lets say when you enter in 319 as the job number, id like it so that the 'client' field and 'product' field match other records that have 319 as the job number.

So that every time I make a new record, I can enter in 319 as the job number and the other fields are already complete with the correct information.

Sorry for being confusing, I'm assuming there is an easy fix to this.

Thanks!

[LaRetta]

Iif you are saying that you want to enter a Job and specify the client and product and then the following records during that data-entry session will use those same values as you enter new records, then go to your auto-enter options on the Client and Product fields and specify 'Last Visited.'

If it is something else, then it doesn't sound like you are taking advantage of relational design. Do you have a Clients table and a Products table? Is a Job assigned to a Client? Are Products already assigned to a Job? If you have a table of Jobs, then the Client CAN'T logically be assigned to the job automatically or you wouldn't be able to have jobs for other clients.

[waymon]

Okay.

So I have 2 tables. Table1 and Table2 with matching layouts.

Both Tables/layouts contain these fields:

Job Number

Editor

Assistant

Producer

Table1/Layout1 gets filled in first.

Im trying to figure out so that when I enter in Job Number in table2/layout2 that it copies over editor, assistant and producer from table1/layout1 into table2/layout2

I'm very new to file maker. Sorry again :-D

Edited July 30, 2010 by Guest

[LaRetta]

No problem about being new to FileMaker ... we ALL were new once. :^)

So then you are not taking advantage of relational design. Please tell us what these two tables have in common. Please use regular words instead of Table A and Table B etc. We need to understand how these tables are related to know how to, ahem ... relate them. Once we know how they should be related, the data can be displayed through the relationship instead of copying it twice.

[waymon]

Okay So here are my two layouts.

------------------------------------------------------------

------------------------------------------------------------

In the first layout you can see the XSAN number. That is the number I want to be the identifier.

I want it so that when you make a new record in image2 and enter in the XSAN Number, it pulls editor, assistant editor and studio producer over from image1's table with the same XSAN Number

Here is my relationship, its nothing.

-----------------------------------------------------------

I just want to be able to type in the XSAN number into Image2 and the editor, assistant editor and producer are auto-filled in.

Hope that isnt too confusing.

Thanks again for your help :-D

Edited July 30, 2010 by Guest

[waymon]

Id just like it so that whenever you enter in the XSAN number, the editor and assistant editor and producer match other records that have the same XSAN Number.

[LaRetta]

Thank you for the additional information. You want to relate Producers to Inside Job Inventory on the XSAN Number.

Go to your graph and point at the XSAN Number in Producers and drag to same field in Inside Job Inventory. Close your graph.

Then go to any layout in Inside Job Inventory where you want the Producer, Producer Assistant and Studio Manager to appear. From layout mode, select Insert > Field. Open the top pop-up (which will list your tables) and select Producers. From the field list, find Producer and insert it on your layout. Repeat with each field.

There is no need to duplicate information in your second table - normalization says you can display data as needed from other related tables or files. The values from your other table (since they are related by XSAN Number) should appear on your layout. If they don't then let us know. And yes, now any time you view this XSAN Number, the same Producer staff will appear on every Inside Job Inventory record with same XSAN Number.

Relational theory is cool. :wink2:

[LaRetta]

Hopefully not to confuse you, since I told you to relate on XSAN Number, in truth you should use FM-generated, auto-enter unique (meaningless) serial numbers to relate your tables. But I want you to understand the theory using a field which makes sense for you first. And you will need to understand how these relate so that you can then change the relationship to true unique keys (as I will now explain).

After you get that relationship working, you should do this (back up first):

Go to Producers and create field (type number) called ProducerID (and in options specify it as auto-enter serial starting at 1, increment by 1). This is called your Primary key because it serializes one of each record in the Producers table. Then in Browse mode, show all records in the table, place your cursor in this new ProducersID field and select Records > Replace Field Contents and select 'Replace with Serial Numbers' and specify 1 and 1. Be sure the checkbox 'update serial number in auto-entry' is CHECKED. This will serialize all of your Producer records.

Now go to Inside Job Inventory. Create a field (type Number) called ProducersID in it. Do NOT serialize this number because you will be filling this in when you create a new record in this table. To bring the existing records up to date, place your cursor in the ProducersID field (after showing all records) and select Records > Replace Field Contents and specify this time 'by calculation' and in the calculation box, select your Producers table and select the ProducersID field. This will, using the existing relationship based upon XSAN Number, now fill your Inside Job Inventory records with the proper ProducersID number.

Now that you have a truly 'proper' relationship, change the connection in your graph and join ProducersID to ProducersID on the =.

Then create a value list called Producers (in File > Manage > Value lists). Specify it as:

Use Values from Field. In top pop-up, select your table (in this case the Producers table). Directly below, select your ProducersID and to the right, select 'also display' and select your XSAN number. Below, specify 'all values' and select 'show values only from second field.'

Lastly, go to your new ProducersID field in your Inside Job Inventory and, in Layout Mode, go to your Inspector tool (the blue [color:blue]i), select your Data table tab and specify under 'control style' pop-up menu and select your Producers value list.

Whew! But this is all important basics to designing with FileMaker. And you now have in place a truly relational system based upon unique primary keys. The ProducersID in your Producers table is called the parent key (or primary key). Some people name the field pk_ProducersID so they know it is primary. The ProducersID (when it exists in any other table), is called the foreign key or some call it the child key and some name it fk_ProducersID so they know it is foreign.

When you create a new record in Inside Job Inventory and select your XSAN number from that pop-up, you establish your proper relationship and the ProducersID is automatically inserted into the field (but it will display the XSAN number because you are using a pop-up). Now all related Producers information is yours to display freely by 'cross placing' fields.

Id just like it so that whenever you enter in the XSAN number, the editor and assistant editor and producer match other records that have the same XSAN Number.

And yes, you will accomplish this request ... in addition, delete the fields for Producer, Assistant Producer and Studio Manager (all those duplicate fields) out of Inside Job Inventory - you don't need them since that information is now related and available to display on any reports, layouts, exports and so on.

Edited July 31, 2010 by Guest
Changed Data 'table' to Data 'tab'