9 Replies Latest reply on Nov 10, 2014 8:34 AM by philmodjunk

    External/extra non-related field in portal

    EmelieLeht

      Title

      External/extra non-related field in portal

      Post

      Hi,

      I wonder if it is possible to create a portal, diplaying related records as usual, but in each portal row also have a field whis is not originated from the related table? The content of this field will be dependent on data from the related record but also on external data or manually entered by the user.

      I don't want to include the field in the related record since this field would change depending on the record where the portal is.

      Any help welcome!

      Best regards,

      Emelie

        • 1. Re: External/extra non-related field in portal
          philmodjunk

          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).

          • 2. Re: External/extra non-related field in portal
            EmelieLeht

            Hi,

            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,
            Emelie
             

            • 3. Re: External/extra non-related field in portal
              philmodjunk

              I don't see why you would need more than one added table occurrence. Can you describe the precise details of your relationships?

              • 4. Re: External/extra non-related field in portal
                EmelieLeht

                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?
                Articles----<Production order

                If so, then we also have:
                Articles---<Article content
                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,

                Emelie

                • 5. Re: External/extra non-related field in portal
                  philmodjunk

                  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.

                  • 6. Re: External/extra non-related field in portal
                    EmelieLeht

                    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.

                    //Emelie

                     

                    • 7. Re: External/extra non-related field in portal
                      philmodjunk

                      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.

                      • 8. Re: External/extra non-related field in portal
                        EmelieLeht

                        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?

                         

                         

                        • 9. Re: External/extra non-related field in portal
                          philmodjunk

                          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----<BOM>-------Materials

                          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-----<BOM>-----Production|BOM

                          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.