What you describe in your post is possible and is, in fact, a fairly routine thing to do in Filemaker, but it most definitely would not be a "non-related field". It would be a field from a table (Tutorial: What are Table Occurrences?) that you link in a relationship to the portal's table (occurrence).
I know that it is possible to work with table occurrances but I am not following how this would be a solution here.
My setup is like this:
I have a Production order layout and table where I store records of batches of articles to be produced. On the production order I want to show subarticles that should be included in the parent article in production. The structure with parent articles and subarticles are stored in another separate table. I made a portal on the Production order layout to the Parent-subarticle table and it shows included articles just fine.
Thing is, now that I want to have an extra field in this portal showing the amount of subarticles to pick for the production. This is dependent of the amount of parent aricles to be produced which the user enters on the Production order. Hence, this extra value will vary and be different for different production orders. I can't really make an extra TO of the parent-subarticle table and store it there, then I would need an extra occurrance of each production order. So, could you explain a little more in depth what you mean with extra TO here?
Thank you and best regard,
I don't see why you would need more than one added table occurrence. Can you describe the precise details of your relationships?
Ok, as this has become a rather large system, this is only a part of it, but I'll try..
The 'center' fo the system is a table of articles with pkArticleNbr links to the Production order table via fkArticleNbr.
Can this be right?
If so, then we also have:
this is where the parent-subarticle structure is kept. Each parent-subarticle is stored as an individual record and these are the ones I have been showing in the portal.
I would rather not change too much in the parent-subarticle records since the strucure as such is important to the production system. That is why I wanted an external field for the amount of articles to be picked for production.
I can also mention that I started to work on another solution where I made a table with ProductionOrderItems (sort of lineitems) which was fetching data from Article content by using GTRR, looping and a bunch of variables for transferring the data. It works but I am not sure if I am very pleased with it, it seems like a lot of code for something that should be much easier. Do you have any spontanious opinion of which is the best solution?
Thank you and best regards,
That is why I wanted an external field for the amount of articles to be picked for production.
But what is the connection (in terms of what you are trying to do, not database design) between the "number of articles to be picked for production" and a given sub article listed in the portal?
I just don't see any logical connection between the two as this would seem to be a value that would be the same on every portal row.
No, ok.. I can see what you're after. In most occations there is only one subarticle incuded in every parent article, but for some items, screws for example, there are several per parent article. This information is also stored in the parent-subarticle record as amount of subarticles per parent article. On my Production order, I therefore want the extra field in the protal row to display the amount to produce*amount of subarticles per parent article. As you said, in most cases the content of the field I want to inroduce in the portal row would be amount to produce but not always.
That tells me that your related table serves as the BOM (Bill of Materials) for the production of a product.
But is your layout based on "articles" or "Production order"?
Either way, the number field specifying the quantity to produce should be a field in production order.
If your layout is based on production order with a portal to article content, a calculation field in article content can multiply the number field from Production Order with the "Number needed for one article) field to compute the total needed for the current production order.
Yes, the layout is based on 'Production order' and the amount to produce is a field in this table, the portal goes to 'article content'.
I have indeed thought of calculation fields but I always assumed that they based their calculation on the latest input. Do you mean that this type of calculationfield could change with which is the current record? Let's say that I have several proudction orders on the same article to procue but in different amounts, will the calculationfield show different amounts depending on the amount to produce in the current record?
You raise a very good point.
I must apologize for not using your table names, but it's much easier for me to explain if I use my names for each of these table occurrences.
Generally speaking, a database set up to manage inventory and the typical manufacturing process has two main sections to it: a) a "specifications" section that documents how each manufactured product is to be made. This typically includes a BOM (Bill of Materials) table where each material used to manufacture that item is listed and b) a section where you document the actual production runs where the inventory levels for manufactured items is increased by the production run and the inventory levels of your materials used in that process undergo a corresponding decrease in production. The BOM table then supplies the info on what materials and how much is consumed to produce one unit of product. (A single computer, car, 2 liter container of soda, or ...) and the planned production quantity is then multiplied against the unit quantities of the BOM table to predict the consumption of materials during a given production run. (actual consumption figures will vary due to material wastage during production.)
Product MFG Specifications:
Products::__pkMaterialID = BOM::_fkProductID
Materials::__pkMaterialID = BOM::_fkMaterialID
Products and materials would be two occurrences of the same data source table in most such set ups so that one table can be used as part of inventory management for both materials and products.
Product Production Run
Production::_fkProductID = BOM::_fkProductID
BOM::gfkProductionID = Production|BOM::__pkProductionID
Production and Production|BOM would be two occurrences of the same data source table and you'd create a new record in Production each time that you schedule a production run. gfkProductionID would be a global field defined in the BOM table and an OnRecordLoad scrpt trigger on the Production layout would be used to update the value with this script:
Set Field [BOM::gfkProductionID ; Production::__pkProductionID ]
Now you can define a calculation field in BOM with this expression:
Production|BOM::ProductionQty * Quantity
And you'll see the correct totals as long as the above script is keeping the global field correctly updated to match to the correct production record.