8 Replies Latest reply on Jun 7, 2010 11:21 AM by nenupharvn

    How to combine information from two portals into one purchase order



      How to combine information from two portals into one purchase order


      Hi to everyone on the Forum

      From this you will know I am a new user,  I am using FM10 ProADV.

      I have modified the FM starter solution Sales Order template into a Quotation Program which does everything that you would like a quotation program to achieve within my industry.

      I work within the Construction Industry and refurbish office buildings, eg. partition offices and suspended ceilings.

      Can someone please advise me how to get past my present hurdels.

      Problem I have is:-

      1)  I create many records of materials for each project I quote for, and materials are also supplied from different suppliers,

      Within each project I could be quoting on 3 different floors and in doing so have records that are from the same suppliers using the same materials but with different quanties from different floors.

      All the materials have a unique ID (which is its part number.) broken down to its code number and or length and or colour and or finish = Part Number

      I can print off a Purchase order of each record with no problem.

      What I would like to do is combine all the same supplier records and print off the material purchase order on one purchase order form, showing the product part numbers with the new combined totals for each product ordered and including any materials from the same supplier that is a one off and only native to that floor, with an option to ignor certain records within the project that have not been accepted.

      Each record produced has the portal with a list of materials used on a project from a chosen supplier linked to the Product Database,(Inventory)


      I hope this is clear, and I thank you in advance for any help or pointers given

      BTW.  Filemaker 10 -   Best move I made in years buying this, what a joy to use, keeps the old grey stuff working.

        • 1. Re: How to combine information from two portals into one purchase order

          First, by far the easiest way to print (or view) line items is on a layout of line items, putting the common info, such as Supplier info, into the Header (or Title Header), the multiple records into the Body, and any grouping desired into Subsummary parts. The "which records" can be established by either a Go To Related Record step, a Find, or a combination of the two. This is basic FileMaker reporting.


          It sounds like you'd want a Subsummary part for Floors (field in line items, numeric: 1, 2, 3). You could either incorporate the "Approved_flag" (number, 1 or nothing, shown as checkbox) into a (new) relationship to the line items, then Go To Related Record, or you could use a Constrain Find after going to related records. 


          Sort by Floor. This will cause the Subsummaries by Floor to appear, with "Floor 1", "Floor 2", etc.. Beneath the Subsummary would be the Body, with the multiple line item records for that floor.


          You do not say whether any particular material may have more than 1 record on a floor (say for different rooms, or part). If so, then the Materials would also require a Subsummary part (below Floors), and be sorted by Material_ID* (after Floor). In this case you'd put all the record's data into that Subsummary part, and delete the Body part entirely, to prevent showing duplicate material entries.


          You'd want a Summary field of whatever your "cost" field is, to put in the various subsummaries and Trailing Grand Summary part, as needed.


          In FileMaker 10 the above breakdown would appear in Browse mode (as well as Preview mode). Very cute.



          * I (and likely other relational design devotees :-) are not crazy about the fact that your material ID is composed of meaningful data, and is really a "material code", not a FileMaker auto-enter serial ID, but I suppose it is OK for this particular use. It could be a problem later if; 1. it changes, and 2. you want to preserve relational links for previous entries.

          • 2. Re: How to combine information from two portals into one purchase order

            Hi Fenton


            Thank you for your quick reply


            I have not explained myself clearly, or I do not understand you reply,  either way, its hard to explain to someone who cannot see what you are doing.......


            basically - all the info required for the purchase order is ready on one record, supplier info, address, etc from a supplier db, site info, contacts and address etc from a project db,

            like the sales order/purchase order bps solutions I have the portal linked to the product db, lineitems which lists all the materials required for that part of the project,

            If this one record was the complete purchase order for the project on one floor - great, but alass because I have broken down the entire project into sections (eg. floor 1, fllor 2, floor 3 etc.) I would have to print off, as in this case 3 purchase orders to the same supplier with simular products, but not the same quanties, one for each floor

            what I am trying to work out is how to print one purchase order combining the three quotes (purchase orders) on one purchase order


            I hope that is clearer


            • 3. Re: How to combine information from two portals into one purchase order

              Basically, there is no need to break this into 3 POs. The line items could fairly easily have been kept on only 1 PO. I can see why you broke them up; because you could not see how to do the floors separately. But it could be done fairly easily on one, either by choosing the floor for each item (1, 2, 3) on one portal, or by splitting the interface into 3 portals (could be in 3 Tabs, if room is needed).


              You could do this by creating 3 dummy "constant" fields in the parent PO table; calculations, with a result of 1, 2 and 3. Create relationships using each of those fields (as well as the PO ID), targeting the Floor field in the line items. Turn on [x] Allow creation of related records. Now, when you enter data into any of the fields in the respective portal, it will automatically assign the floor number. Hence the appearance and functionality of 3 floors, but in one PO, with its line items.


              [ Keep the original PO ID=LineItems::PO ID relationship also, as it is the main one for many operations, where you don't care which floor it's on.]


              There should be clear logic about what the "entity" of a table is, though it needs to be determined in each case. In this case the entity is: a material entry for a particular purchase order. They belong to only 1 PO. The fact that you need to sometimes see and use an entity as on 3 different floors is more of an interface and display issue; it is not a structural issue, in my opinion.


              You could keep your current structure, and somehow tie those 3 POs together. But that just gets uglier (like, tie them together with what? when? how?). Splitting at a higher level creates more problems than it solves; it is usually unnecessary.

              • 4. Re: How to combine information from two portals into one purchase order

                Hi Fenton


                I have read your reply, a few times in fact.

                Thank you for the info, I am just amazed at the solution, why didint I think to try that,

                It seems so simple and yet logical

                I will be given this a try tomorrow, its getting late now, I will let you know how I get on

                once again thank you and have a happy new year



                • 5. Re: How to combine information from two portals into one purchase order


                  I like the concept of the solution that has been posted previously, this has given me food for thought,   but..........


                  for now I am trying to find a solution as described further down,

                  the problem is at present I use Excel to carry out all my quotations, depending on size of project up to 30 tabs are used

                  a project is a collection of small jobs, each job has two tabs,

                  one for a dedicated supplier with its material take off list with a material cost at the end

                  and the other tab is for a dedicated labour contractor given the breakdown for that job with a labour cost


                  I am using filemaker 10 pro Adv. and have revamped the bps.solution to my requirements including creating new tables and layouts etc.

                  everything works great, except I cannot get past this next hurdle, cannot seem to figure this one out.


                  The idea is to amalgamate all the chosen records from a Quotation table and within the same table send the results to a purchase order layout preview, before printing.

                  Below, two separate records (Record 2165 & Record 2168) with similar products from the same supplier, from the same project displayed in their respective record portals, product details taken from a linked products table.

                  It could be 3 records or even 5 records listed, but for this purpose I have shown the portals from the two records.


                  Record (Quotation ID 2165)

                  Supplier - ABC Supplies - from Suppliers table

                  Project Number - 3987


                  Code      Description                                       Length      Finish           Qty               Price           %D/C       Amount

                  102      Reversible Head Channel                 3658        RAL 1006          2              33.66         25%         50.49

                  109      32mm X 3mm Flat                          3000        RAL 1006          6              15.73         25%         70.79

                  110      Heavy Post                                  6000        RAL 1006          6              184.28        25%        829.26

                  351       25MM X 6 Flange Head Screw         Box         Zinc                 7               10.32        40%         43.34

                  614      75mm Plastic Skirting                     3000        Black               6                7.21         25%         32.45

                  821      75mm Head and Wall Channel          3658        RAL 1006          2               48.94        25%         73.41

                  601      Upright Post                                 3000        RAL 1006          6              22.48         25%        101.16


                                                                                                                                                  TOTAL     1,200.90


                  Record (Quotation ID 2168)

                  Supplier – ABC Supplies – from Suppliers table

                  Project Number - 3987


                  Code     Description                                       Length       Finish           Qty              Price           %D/C         Amount

                  102      Reversible Head Channel                 3658        RAL 1006          4             33.66          25%           100.98

                  109      32mm x 3mm Flat                          3000        RAL 1006          5             15.73          25%             58.99

                  110      Heavy Post                                  6000        RAL 1006          2            184.28          25%           276.42

                  614      75mm Plastic Skirting                     3000        Black               10             7.21           25%            54.08

                  821      75mm Head and Wall Channel          3658        RAL 1006           1             48.94          25%            36.71

                  903      Kompanel                                    3000         Natural             5             37.82           17.5%        156.01

                  909      Plasterboard Square Edge               3000         Natural            10           14.00            54%           64.40


                                                                                                                                                   TOTAL         747.59




                  Displayed below is what I would like to see on my Purchase Order Layout preview before printing the order...........

                  Both records above combined, duplicated line-item products removed to form a single line item with their totals added together

                  Any other line-items specific to any of the selected orders from the same project and supplier account also added to list




                  Code    Description                                        Length        Finish         Qty             Price             %D/C           Amount

                  102      Reversible Head Channel                3658          RAL 1006        6            33.66            25%             151.47

                  109      32mm x 3mm Flat                         3000          RAL 1006        11           15.73            25%             129.77

                  110      Heavy Post                                 6000          RAL 1006        8           184.28            25%            1105.68

                  351      25MM X 6 Flangehead Screw          Box           Zinc               7             10.32            40%               43.34

                  614      75mm Plastic Skirting                    3000          Black             16             7.21             25%              86.52

                  821      75mm Head and Wall Channel         3658          RAL 1006         3            48.94             25%             110.12

                  601      Upright Post                                3000          RAL 1006         6            22.48             25%             101.16

                  903      Kompanel                                    3000          Natural           5            37.82            17.5%           156.01

                  909      Plasterboard Square Edge              3000          Natural          10            14.00             54%             64.40


                                                                                                                                                      TOTAL      1948.47



                  I hope this is clearer, sorry about the layout's (no format)

                  If anyone has a solution to this (Main Priority) or can suggest any other methods and examples to share I would be very grateful


                  I am still learning and trying to understand more on how to use filemaker,

                  can anyone suggest any further reading materials especially about scripts and Functions explained, sites to visit 

                  Thank you 

                  • 6. Re: How to combine information from two portals into one purchase order

                    I don't really know why you're doing this in Excel. Perhaps because it's already there? (not a good reason). Perhaps because there's all kinds of complex calculations, which you don't want to transfer to FileMaker? (better reason, but could be moved to FileMaker). Or both complex and flat, with hundreds (thousands) of fields. Well, a much better reason, but still...


                    The fact that you've done it in separate files or worksheets in Excel is no real reason why it must be separate Projects in FileMaker. It just means that you have to do a little more work, either before or after import. Personally, I will not build an incorrect data structure in FileMaker just because someone has supplied me with data which is not so great, is not properly normalized; because people often do.


                    So, the structure in FileMaker is still the same. There is a Project. It has different Floors (at least that's what you said earlier). There is a Supplier for each line.* So, each line item record needs the Project ID, a Floor#, and a Supplier ID.* That's all you need to: 1. Tie them when needed, and 2. Separate them when needed.

                    So you need to put those fields into the data either after Import, via Replace. Or before Import, by using Fill Down in Excel. There are a few ways this could be done with a minimum of fuss. But it would definitely be 1 Import per  


                    As far as I can see, the Quotation is not a real entity, nor can I see any real method to produce that number (I don't really consider manually entering a number as a valid method). Maybe it was just created because of Excel. It seems to take the place of what you earlier called Floors, which served the same function.


                    The line items for the purchase order would have a Subsummary by what you called Code, with Summary fields for Qty and Amount; so that each Code only appeared once, summarized.


                    Purchase Orders would be a separate table. But it could share the same line items as Project, maybe. Or you could summarize all the line items from Project, and Import them into a PO Line Items table. It depends on the complexity of what you're going to do with them. It is easier to deal with missing, damaged, returned items if they are a separate table. But they still should be tied to the original Project Line Items, just to keep track of what's been ordered, what has come in for the Project, etc..


                    This is not really simple. But starting off with the correct data structure would be a good first step, in my opinion. Multiple records for each "floor" of a Project seems to me an artificial structure. Though it could be done correctly, by having a Project_Floors sub-table of Projects; as you could then see all the items from Projects still. But I'd need a better reason than "that's the way they are in the Excel files."


                    *The Supplier could be chosen later. It could also be done for the summarized items in a different PO Line Items table, if you decide to do that. It may also be that you already KNOW which Supplier goes with each Code item, and you have a Materials table with that data in it. In which case you could just Look Up the SupplierID, with no further interaction. But that would have to be populated ahead of time.

                    • 7. Re: How to combine information from two portals into one purchase order

                      Hi Again


                      I am a newbie to Filemaker 10 pro adv.


                      I choose this software after trying it out on a small scale from an earlier version that had been discarded, I liked the way it worked so I purchased FM10 Pro Adv.

                      So I am still learning to use it and attempting to use best practices,  Up hill learning curve, it all takes time


                      The reason I spoke about Excel is because I have been using it for years, and a few months ago I said there has to be a better way to do what I am doing.


                      basically, I have not made myself clear and or have giving you the wrong perception of what I do and trying to do, I have tried to explain as quickly and simply as possible to no avail, just not knowing how to express the problem clearly.


                      I have utilized bps.solutions (as I figured to form a good solution by combining what I have done in excel and other programs) to my advantage but unable to get the combined invoice section working. (as per previous reply)


                      The logic behind what I am asking is to explain what I do clearly, so here goes.


                      As an Interior Construction Contractor


                      I refurbish Office Interiors, we supply and install Partition wall layouts, Suspended Ceilings, Electrical works Etc.


                      The excel layout is each workbook is a project, within that project,  we use a formatted layout on each sheet (job sheet) where we enter the materials or labour rates for a specific job within the project.


                      Each sheet is given its job title (e.g. first floor partitions OR first floor Ceilings OR even Ground Floor Tea Point Area) it has its own total cell at the bottom of the sheet,

                      we pair these sheets into two's - one for materials and one for the labour content, we keep these separate for issuing purchase orders, basically two different suppliers.

                      we break projects down to bite size jobs and these are separate quotes in their own right, it gives us the versatility to edit or modify each job or even freeze the job.

                      this is because of client changes, he may want a full height door in the partition wall instead of a standed height door, or the client changes the solid wall to full height glass wall or even shelves the idea of having a office in that location of the building. Any changes made affects the material take off and the labour cost of that job.

                      Regardless of how many job sheets we use on the project they are totaled up on a totals sheet at the end.


                      so we end up with this on the last totals sheet 


                      1st Floor end partitioning               2,600

                      1st floor entrance partitions           3,750

                      1st floor tea point area                 1,975

                      3rd floor meeting room                  6,500

                      3rd floor video conferencing eqp     10,850

                      ground floor reception                   3,925


                      Project total                              XXXXXX



                      This above bit I end up with


                      we print the purchase orders from the take off sheets has they have not been corrupted by and additional charges or mark ups


                      We have approximately 18 material suppliers and 16 labour contractors that we use

                      and certain materials come from branded suppliers, so we could have two different manufactured systems supplied on the same floor from two different suppliers but installed by the same labour contractor, or any other combination of suppliers and labour contractors

                      so originally I was asking how to pull the line-items of two or more records sort / merge / add (for want of a description) to be forward to a purchase order layout preview form ready for printing.


                      Does this give you a clearer insight of what I am trying to do


                      Question, am I wrong in what I am asking to do with the bps.solutions I am using, can it be achieved or do I have to go back to the drawing board and rethink,

                      if so I really need to understand what is required.

                      at this time everything I have put together works except the purchase order bit.

                      • 8. Re: How to combine information from two portals into one purchase order

                        These are many positions of  Purchasing department. It can include:


                        1. Purchasing supervisor
                        2. Purchasing executive
                        3. Purchasing director
                        4. Purchasing specialist
                        5. Purchasing assistant
                        6. Purchasing clerk
                        7. Purchasing manager
                        8. Purchasing associate
                        9. Purchasing assistant manager

                        10.  Purchasing officer

                        11.  Purchasing coordinator


                        Based on the above positions can help you to set up Purchasing dept, design job descriptions, Purchasing interview questions.


                        Apart from that, this link below may be useful: http://www.humanresources.hrvinet.com/purchasing-job-description

                        I hope that this comment can help some info for our communities.