Auto-fill a field based on a second table

[Noam]

Hi,

I tried to find my answer in previous posts... but I am not familiar enough with programming so I could not understand...

What I want to do.

I have table1 with 2 fields. A number and a name. I have many records, each with their name associated to a number.

In table2 I have a more complexe table with several fields. Two of them are that number and name. In that data base, those number/name can come back many times.

I would like to be able to just enter the number and have the name auto-fill automatically.

I did create a relationship between those fields across the tables. Then on table2, I told the Name Field to use lookup and copy value from Name Field (from table1).

It didn’t work. I tried the same with lookup the Number. Then lookup both... I’m lost! I tried the calculating option... but I know nothing about programming....

 

Thank you!!!

[comment]

3 hours ago, Noam said:

I did create a relationship between those fields across the tables. Then on table2, I told the Name Field to use lookup and copy value from Name Field (from table1).

This should work - provided you set it up correctly. The relationship should match on the Number fields, and the Name field in the target table should be defined to lookup its value from the Name field in the source table.

Since we don't know exactly what you did, we cannot tell you where you went wrong.

 

[Noam]

Thank you, I will try again.

-do I have to use the lookup for both fields (number and name)? Or just for the name, since I will type the number manually?

- when you create a relationship, there are check boxes under both tables. “Allow to create record across tables/allow to delete records/sort records” . Should I check any of them?

The thing is, in table1 I have about 20 records with each a different number/name but in table2 I have 300 records where those number/name come back many times... So I don’t know if I can “allow” them to create records... 

- One other thing I might do wrong... In “manage layouts” my table2 is ‘using data from table2” (and so are all of my other layouts, everything works fine)

My table1 is currently “using data from table1”.... should I switch it to using data from table2? 
 

- I did table2 first, and then I decided to try having a list of number/name to auto-fill so it would take less time. So my list in table1 is not complete with the 20 number/names since I used just 2-3 for the test. 
Do they have to be created in order for it to work? Do I have to complete table1 with the 20 number/names for it to work?

 

Thank you so much!!!

I’m sorry, I’m teaching file maker to myself and that’s how far I could go... Thanks for your help!!

 

[comment]

Before I answer your questions, can you explain what is the purpose of this? What do your tables represent in real life? 

The (main) reason I ask is this: you say you want to enter the number and have the name filled automatically. Filemaker provides two ways of doing this: you can (1) copy the name from the source table into the target table; or (2) display the name from the source table in the layout of the target table. The difference between these two is what happens when the name is modified.

 

[Noam]

I use file maker to organise the costumes for a film/tv show.

Table2 has many fields which I fill up manually based on documents the production gives me. Each character has a number. When I fill Table2, I only fill the character number, because it takes less time. Each record represent a character in a scene. (Number/name fields + scene number and other fields)

After that, I made different layouts (triggered by scripts) that organize the information of each scene in orders that I need. Mostly, the sort orders come from the Number I associate to each character. 
The value in the Name field is never used in the sorting order, but it always appear somewhere in the different layouts.

What I want, is that while I create manually each records, I can only type the number and then the name of the character auto-fill.

So I guess I would have to fill Table1 with the number/name prior to that.

(In that specific case, I did it after...  because I thought of it after... so table2 is all fill up with the numbers,  but the Name field is still empty... and I’m trying to find a way I don’t have to fill it manually)

Does that help you understand what I try to do?

Thanks!!

[comment]

So basically, you have a table of Characters and a table of Costumes. In such case, I don't see a need to copy the character's name into a field in Costumes. Simply place the Name field from Characters onto the layout of Costumes (and make it non-enterable so it cannot be modified  accidentally). The relationship should be the same as before: matching on the number field (for which the proper name is CharacterID).

2 hours ago, Noam said:

when you create a relationship, there are check boxes under both tables. “Allow to create record across tables/allow to delete records/sort records” . Should I check any of them?

If you allow the relationship to create records in the Costumes tables, you will be able to add costumes to a character by entering them into a portal to Costumes placed on a layout of Characters. From your description, it doesn't sound like you intend to do that.

If you allow the relationship to delete records in the Costumes table, then any time you delete a character, you will also delete all their costumes. You definitely do NOT want to allow the relationship to delete records in the Characters table because then deleting a costume would also delete its parent character - and leave all their other costumes orphans.

 

Edited January 28, 2021 by comment

[Noam]

Thank you very much for your time!! I will try that!