1 Reply Latest reply on Dec 20, 2016 10:28 AM by philmodjunk

    Noob question about a multi-record layout


      My apologies in advance if I'm too blind to see the obvious...I am still relearning this software and I think I may be making my life more difficult than it needs to be...


      I am building an Estimating & related document database for my company (converting from Excel).

      I have a layout where sales can enter data into a record with an "Estimating Ref No." assigned to it which for the most part is the key record for searches.  From the initial layout, I carry across some of that data to my estimating layout where I establish my pricing.  From there I want to generate a new layout called "Quote Cover Letter".  More often than not this cover letter will only have one line item on it which will be easily referenced by the est ref no I mentioned earlier.  But...on occasion a sales rep may need 3 or 4 quotes for the same customer...and rather than generate a different cover letter for each estimate reference no...I'd like to be able to put multiple reference numbers and their associated record info onto a single layout.  My guess is that the reason I am struggling to get the records onto one document is because all of the fields I need are in the same table.  Therefor I can't find a way to get the details to switch on each grouping of data based on a keyed in ref no. (see below)



      I assumed that the first record would populate based on whatever record I am currently working on and then I could have these fields repeating and just key in the next reference number and have the partID and specs fill in accordingly.


      What am I missing?



        • 1. Re: Noob question about a multi-record layout

          Yes, you need to alter your design. Also, you appear to be treating "layout" and "table" as the same thing in your description. Be aware that while you may have a table on the tables tab in manage | database, a "box" on the relationships tab and a layout all with the same name, they are three different objects with different roles to play in your solution.


          Typically, estimates, invoices and purchase order all follow the same basic data model in relational databases such as FileMaker. The only difference is in the name of the tables.


          Estimate-----<LineItems>-------Products (and/or Services)

          Estimate::__pkEstimateID = LineItems::_fkEstimateID

          Products::__pkProductID = LineItems::_fkProductID


          The last table may not exist if the products or services are customized to the point of being unique to each estimate.


          A common way to set up your layouts is to set up a data entry layout for creating estimates where you base the layout on Estimate, but include a portal to LineItems for listing the details that make up that estimate. Calculation fields in the line items table can compute a line item cost (unit price times quantity) and other calculation fields in Estimates can compute a total estimate cost.


          For printing purposes in FileMaker, we often turn this around a bit and set up a list view layout based on LineItems that includes fields from Estimate placed in Header, footer and/or grand summary layout parts. You pull up a found set of all line items for a given estimate (there are several ways to do this), sort it the way you want and you are good to print or save as PDF. This works better than printing from the data entry layout with a portal as you can print out estimates that are more than a page in length and which don't have a lot of "wasted empty space" for not needed additional line items.


          I've probably used a number of terms that are new to you here. Please look them up in Help, so some researching and feel free to ask for clarification here.