2 Replies Latest reply on Jul 14, 2011 4:55 PM by krancmm_1

    Using the list view to show several entries that share a comman fields (purchase req database)



      Using the list view to show several entries that share a comman fields (purchase req database)


      Hi there,

      I am a FMP newbie, I just learned the basics using an online coarse (lynda.com) and it has been great but I can't seem to make sense of this one and I am afraid I may have done something wrong at the outset of my design.

      So I am making a purchase requisition database.

      When a new record is created it is automatically given a serial number PR 001 ect.

      Using a portal to a line items table the user can enter several line items that will all share the same PR number

      the line item table is related to the PR table by the serial number.

      So when I try to crate a list view showing all of the line items in a single pr that share the same serial it does not work and only shows me the first line item from each PR.

      I imagine that this is something silly that I don't understand as of yet... any help would be appriciated, please let me know If i need to give any more details.



        • 1. Re: Using the list view to show several entries that share a comman fields (purchase req database)

          Just creating the layout is not enough. You also have to perform a find, (by hand or in a script) or use Go To Related Records in a script to pull up those records in a found set.

          A scripted find might look similar to this:

          Set variable [$PR ; Value::PRTable::PR Number]
          Go To Layout [Line Items]//specify the layout based on line items that you want to use here
          Enter Find Mode [] //clear the pause check box
          Set Field [LineItems::PR Number ; $PR]
          Set Error Capture [on]
          Perform Find[]

          Note: this 6 line script can be replaced with a one line script that uses Go To Related Records.

          • 2. Re: Using the list view to show several entries that share a comman fields (purchase req database)

            If you want a list of all PRs with associated line items, you can do this without scripts or GTTR by using a Sub-Summary Part in a List layout.

            Go to Manage... Layouts.  Make a new layout based on Line Items table, List view.  You can add the body Line Item field during the layout setup or later.

            Don't use labels for any of the fields.  Labels go in the header only.

            As you can't select a Sub-Summary Part in the initial setup, go to Layout Mode, go to menu Insert...Part.  That opens a dialog box with lots o' options.  Click on Sub-Summary When Sorted By.  The right side shows the fields from Line Items (Current).  Change the top separate row (tables) to the PR Table.  Then select the PR Primary Key Field (that I assume is used to link PR with Line Items).

            After you click ok, another dialog asks whether the sub-summary should go above the body or after.  Typically you'd choose above.  After you click ok, the sub-summary part is inserted above the body, but won't have any fields in it.

            Add a field to the sub-summary part, the data coming from the PR Primary Key Field (you can add other fields if you want to identify the PR).

            Then add the body fields, or arrange the body fields, in the Body Part.

            When you go to Browse Mode the first time, it will look like nothing happened - just a long list of line items.  To get the sub-summary working, go to Sort and sort on PR primary key.  When you hit ok, you should see each PR with its associated line items under it.

            Once it's set up the list automatically shows all up to date PRs with associated line items whenever you open the layout.  You can do finds just like any other layout (one PR or a range).  You can also do finds on the line items as well, but may need to sort again to get the associated PRs showing.

            Hope this helps (I'm also pretty new at Filemaker).