To start, you can define this relationship:
Documents::Documentid = Labels::Labels_Documentid
Then, instead of a looping script, all you need is a replace field contents script step with the calculation option:
Replace Field Contents [no dialog; Labels::DocumentsidFk; Documents::id ]
If you have an existing relationship between the two tables with different match fields, you may need to use a new Tutorial: What are Table Occurrences? of Documents to do this without altering existing relationships.
Thank you very much for your input once again. I don't seem to grasp entirely the difference between relating to tables via Id = IdFk... I thought that linking the Ids would also work.
Both your id fields are auto-entered serial numbers so there is nothing in place here to link the id in one table to the id of the other as the values will not, except by sheer accident, match.
Normally you match the pk of one table to the fk of another:
ParentTable::__pkParentTableID = ChildTable::_fkParentTableID
where the __pk field is defined to auto-enter either a serial number or Get (UUID ). And the _fk field is a simple data field of the same data type as the __pk field. (Usually, auto-entered serial numbers are entered into number fields and UUID are entered into text fields.)
This establishes a relationship where one record in ParentTable can link to any number of records in ChildTable by putting a value in the _fk field of each linked child record that is exactly the same as the value in the unique value in the __pk field of the parent record.
Ok, but I HAD linked a ParentTableID to a ChildTableIdFk. It's just that my ChildTable also has a auto serial Id, but I didn't link anything to it.
Let me check if I've understood your explanation:
Let's assume I have a ParentTable with contacts and a ChildTable with phone numbers and that I've linked the ParentTableId (auto-serial) to the ChildTableIdFk (no auto-serial).
If a contact with the Id=21 has 5 phone numbers, I can link them to it by assigning the same value (21) to each of the phone number?
I guess tutorials, youtube videos and manuals of instructions can only get me so far...
Thank you again.
You are correct and there are ways to set up FileMaker so that the matching value is entered for you.
If you set up a portal to that table of phone numbers and you enable "allow creation of records via this relationship" in the relationship settings, you can enter data directly in the portal and the pk value is automatically copied into the define fk match field of the new record thus created in the portal's table.
I have another, more specific question, hope you or anyone else can give me some ideas.
That's the scenario:
- a list of 36 products
- a list of 180 materials
- a list of 1800 documents
- a list of 110 equipments
- a list of 8 testing methods
What I need is to link everything in a complex way, so that I get all the documentation to each product according to this process:
A product has documentation of its own, and is made of materials, which have documentation as well. A product is made with some kind of equipment, which has also documentation, and the product is tested in specific ways, according to the documentation.
What would be a good way to link them all together, so that for each product a list is shown, where u can see every documentation of every step in that process?
I already have the lists on an excel-sheet, so it would be about importing data and linking it one to another.
I hope I've made myself abundantly clear. If not, just tell me and I will try to explain it further.
Thank you very much
Can the same document be linked to more than one Product, material, or equipment item?
Am I correct that this is in support of a manufacturing process and you need to manage the specifications for each product manufactured and each material used in the manufacturing process?
If so, this is familiar territory as I have previously managed such specs in a relational database for a manufacturing plant.
Yes it can. And the same equipment is also used for more than one product, consequently, the same type of material can be used in more than one product, but that's kinda obvious. A testing method can also be applied to various products.
And you are assuming correctly. At the end, I need to have a "sheet" or let's say layout, where every equipment, material and document is shown that is related to the product itself or to the manufacturing process. There is actually no need to list the equipment or material, but the documents related to them.
What you have is the need to implement a number of many to many relationships. One of the most basic, is to set up a Bill of Materials (BOM) for each item that you manufacture in order to list each material used to manufacture that item along with the quantities of each material used to manufacture one product or one unit of product. The BOM serves as a "join" table linking any given product to many different materials and any given material to many different products.
I realize that you don't want to "list the equipment or material" but without that relationship, you can't link your documents to different materials and yet produce your combined set of documents for all materials used to manufacture a given product.
So your initial relationships to which many more would be added are:
Products::__pkProductID = BOM::_fkProductID
Materials::__pkMaterialID = BOM::_fkMaterialID
And in many such systems, Products and Materials are really the same table. In FileMaker, we'd do that by making them two Tutorial: What are Table Occurrences? with the same data source table and __pkProductID and __pkMaterialID then become the same field from the same table.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
To extend this concept, you can set up a similar join table to link a product to a list of all the equipment used to manufacture it. And a single table of Documents can, if needed be linked via join tables to table occurrences of products, equipment and materials.
I have here a screenshot of my relationship view. I don't know if that's gonna work out, but then again, I may be lucky after all. =)
Red are the table occurences for all my products, therefore named ProdPool.
Green are those for all my equipments, hence EQ_Pool.
Gray are the table occurences which don't have any primary values and which are only there to connect my pools to each other, therefore x_blabla. They only store values from other tables, normally as IdFks.
Around the gray table occurences you will find those two/three pools which need to be connected. I thought it best to create multiple table occurences.
I hope it's a little clearer now. It's obviously not finished yet, but it may help you understand, what I have and what I need. And of course, point out any mistake you can find.
FYI, I didn't see your post before posting mine.
I've started to import my data to the database via Excel-Files, which worked very well.
As you can see in the screenshot, every QC_method (blue) there is one or many documents out of the DokPool (orange). I've made the connections via the grey table x_QCDok.
Previously I've made a similar connection between the product (red) and the QC-method (blue) via the grey table x_ProdQC.
What I want now is to being able base a layout on the ProdPool and show every related document via the QC_methods. But even though the grey connection tables are fine, something isn't quite working out. I've created this layout and made a portal to the occurence "DokPool QCDok". I've been able to create this portal, so I think some relations must be working, otherwise I couldn't have chosen the "DokPool QVDok" as the target for my portal. And even though I specify which fields of the occurence I want to have shown, there are no entries.
Does anybody understand my problem (did I explain it well enough?) and has some useful input for me? That would be more than great!
If you need more input from me, pls tell me so.
Thank you and best regards
As you can see in the screenshot,
Don't see any screen shot unless you are referring to the one you uploaded nearly a month ago...
Yes, I was referring to that one. But I'll upload another one right now. This one is up to date, too.