8 Replies Latest reply on May 2, 2012 9:56 AM by philmodjunk

    Dynamic Sheet Using Portals



      Dynamic Sheet Using Portals




      I am trying to make a more efficent database with our work order system.


      I had a database that has 6 items per work order. And what we do is if its more than six items we put it on another work order to add the items.


      What I would like to do is have a work order that can grow and shrink depending on the number of items.


      Like you have a place to put how many items are going to be on the work order and it would make that many lines.


      I was thinking I could do it with a portal but I have no idea if it is even possible. 


      So I wanted input on it before I start tryin to accomplish the impossible lol.





        • 1. Re: Dynamic Sheet Using Portals

          Yes a portal can work for this if ;you set up tables, layouts and relationships to support it.

          Workorders have the same structure as Invoices:



          In both cases, a portal to line items can be used for data entry and a layout based on the lineitems table can be set up for printing the invoice or work order.

          Here's a simple demo file on invoicing you can examine. Just imagine that "invoice" has been renamed as "workOrder" and it should provide a useful example: http://fmforums.com/forum/showpost.php?post/309136/

          • 2. Re: Dynamic Sheet Using Portals

            It isn't going to work for what I am tryin to do, I have to many fields that require user input.


            I changed it to list view and it looks good. I have another question tho, we want to keep the same work order number as long as the PO No for the job is the same. 


            So would it be possible to set it up so that when you create a new record it will pull the previous info that was inputted and then when you need a new work order you can just put new work order?


            I have the contact info in the header and in the footer I have a job process and in the body is the actual work order info.


            I was thinking (if it was possible) when you select new record a pop up box comes up and ask how many items will you be inputing, and it automatically create that many items and look up the info inputed on the first record.


            Attached is a picture of the layout.

            • 3. Re: Dynamic Sheet Using Portals

              You could use show custom dialog with an input field to a global number field to ask the user for the number of items, but I suggest you try a simpler aproach first:

              a) the number of fields shown in your screen shot can easilty by handled in a portal. You can resize the portal row to get two rows of fields to each portal row if you want that.

              b) You can load a global variable or global field with the ID number of the parent record and set an auto-enter calculation to enter the value of that field or variable into the matching ID field of the line item table each time a new line item record is created. All you need is a script that creates the new Work Order record and then copies the new records serial number into that variable or field.

              • 4. Re: Dynamic Sheet Using Portals

                How would I go about setting up option (b) I would need to set the first record inputted to be the parent record.


                I am not to keen on scripts yet

                • 5. Re: Dynamic Sheet Using Portals

                  I am not to keen on scripts yet

                  In which case, maybe you should reconsider option a). It does not require any scripting to set up.

                  You can set up a trigger so that when you navigate to the Work Orders layout, a variable is updated with the WorkOrder ID. A script can also switch to the Work Order layout and create a new record to start a new work order.

                  Let's assume your tables are named and related like this:

                  WorkOrder::WorkOrderID = LineItems::WorkOrderID

                  and WorkOrder::WorkOrderID is an auto-entered serial number.

                  Then this one line script:

                  Set Variable [$$WOID ; value: WorkOrder::WorkOrderID ]

                  will load the global variable $$WOID with the current work order's serial number. On the WorkOrder layout, enter layout mode and select layout setup... Click the script triggers tab and use OnRecordLoad to perform the above script.

                  Now open Manage | Database | Fields, find the LineItems::WorkOrderID field and double click it to open field options. Click the auto enter tab and select the calculation option. Enter this single term: $$WOID as the calculation. Click ok. Leave the "Do not replace existing value..." option selected.

                  Now you can find a given work order on the work order layout and this will trip the above script trigger and update $$WOID with that record's serial number. If you then switch to the LineItems layout, any new records you create will be automatically linked to the WorkOrder record. If you add a button for New Work Order on the Line Items layout, it could run a script like this:

                  Go to Layout[WorkOrders]
                  New Record/Request

                  And this script will both create a new record and also will trip the OnRecordLoad trigger--causing that script to be performed and update the $$WOID variable accordingly.

                  This script, performed from the WorkOrder Layout, will pull up all the lineItems records for that WorkOrder on the LineItems layout:

                  Go To Related Record [Show only related records; From table: LineItems; Using layout: "LineItems" (LineItems)]

                  • 6. Re: Dynamic Sheet Using Portals

                    Got it :D


                    • 7. Re: Dynamic Sheet Using Portals

                      I looked at the invoice database you attached and i am trying to implement that into a delivery ticket system.


                      I have the work orders in a list but we dont send everything at one time on the same workorder.


                      So I am trying to get it to display items on the delivery ticket via a drop down item. (like the invoice database) but I cant get it

                      to let me add another item. 


                      Attached are examples thanks again.

                      • 8. Re: Dynamic Sheet Using Portals

                        Open Manage | Database | relationships

                        Find the table occurrence "boxes" for your layout and your portal. Double click the line that connects them. Make sure that "allow creation of records via this relationship" is enabled for the portal's table occurrence.