4 Replies Latest reply on Oct 22, 2009 10:59 AM by cosmo

    Showing / Hiding Records

    cosmo

      Title

      Showing / Hiding Records

      Post

      Good afternoon -

       

      I put together two layouts (Purchase Orders and Check Reqeusts) using one table and global fields so each form will have the next available number (i.e., PO #55000, 55001, 55002, etc; Check Request CR11, CR12, CR13, etc).

       

      My question is - since both forms are using the same table - all records appear (4,000+) when opening the form.  When selecting either the PO or Check Request, how can I show the records for that particular form (i.e., PO record quantity - 3,500; Check Request - 500).  Do I need to put a script together, or is there an easier way?

       

      Thanks in advance for your assistance.

       

         

        • 1. Re: Showing / Hiding Records
          philmodjunk
              

          Warning: If you are using global fields to store the next Purchase Order and Check numbers, your database will fail if you should try sharing it over a network.

           

          Are you sure you don't want to store the data in two separate tables?

           

          Then the check and purchase order records can be set up as auto-entered serial numbers and that will simplify that aspect greatly. You can then have a different layout for each type with a different underlying table that only contains the one type of record.

           

          If you insist on your current table structure, you can do what you want with some simple scripts. If you are using FMP 10, you can set it up with a script trigger.

           

          Using just the info you post, this script should find all Check Requests:

           

          Enter Find Mode[]

          Set field [yourtable::yourcheckReqField; "CR*"]

          Perform find []

           

          This script should find all PO's:

          Enter Find Mode[]

          Set field [yourtable::yourcheckReqField; "CR*"]

          Omit Record

          Perform find []

           

          If you use layout setup... to set the on layout load script trigger for each layout, you can trigger the appropriate script to find just check requests or just purchase orders.

          • 2. Re: Showing / Hiding Records
            cosmo
              

            Good morning and thanks for the reply. 

             

            I will try the script, but the networking issue does concern me, so I will need to look into this.  However - I am interested in hearing other options if you have any to share.  Let me provide a bit more information on what I've done with the database.

             

            The Check Request and PO were originally two separate tables.  However, my goal is to have the check request and PO use the same Supplier table, with PO Costs, PO Actual Costs, and Check Request Costs being entered and applies (shown in a portal) in a Job Ticket Table and the Supplier Table.  The actual costs from the Check Request and PO Actual Costs should add together and be deducted from a budget that is appearing in job ticket record.

             

            I tried to keep the Check Request Table and PO Table separate, but could not get the two to work where the could appear in one portal within a job or contact record. 

             

            I have combined the two using one table which does have a unique serial number, but set up the global field so numbers would automate in numerical order depending on what was selected next (i.e., PO1, PO2, PO3, etc. vs. PO1, PO2, CR3, CR4, PO5, etc.).

             

            Again - I welcome any recommendations you may have.

             

            Thanks for your help and time.

            • 3. Re: Showing / Hiding Records
              philmodjunk
                

              Unfortunately, I see place holders for pictures in your post, but not the pictures. My internet connection has been very flaky so it may be a problem on my end.

               

              The problem with using a global field in this manner is that changes made to the global field by one user will not be seen by another user. It's as though they have separate "virtual copies" of the same field. Furthermore, any change a user makes to the global field by a user will not persist. When a user closes the database and re-opens it, the value in the global field will have reverted back to its original value.

               

              I don't follow why you want the records in the same table.

               

              "my goal is to have the check request and PO use the same Supplier table"

              You don't need to have CR's and PO's in the same table to do this. You can link both the CR and PO tables to the same Supplier table.

               

              "The actual costs from the Check Request and PO Actual Costs should add together and be deducted from a budget that is appearing in job ticket record."

              Again, this can be done with relational links between the tables in question and both the CR and PO tables. You shouldn't need to combine the records in a single table in order to do this.

               

              "I tried to keep the Check Request Table and PO Table separate, but could not get the two to work where the could appear in one portal within a job or contact record. "

              You might be able to do this with a special "join" table or surely you could place two portals side by side or in separate tabs of a tab control?

               

              Nevertheless, this is your database, and the pictures I can't see may explain better why you need to keep the two kinds of records in a single table. Here's a method that avoids the global field - Network issues for applying a separate serial number to each field.

               

              Define a new table, CR_Serial_Nos and give it just two fields: CRPO_ID and CR_Serial.

              Define the CR_Serial field as an auto-entered serial number that starts with the letters "CR".

              Link this new table to your Check Request/Purchase Order Table: (CRPO is what I'm calling it.)

               

              CRPO::CRPO_ID = CR_Serial_Nos::CRPO_ID  // Enable "Allow create records via this relationship for CR_Serial_Nos

               

              Now, whenever you want to assign a Check Request serial number to a given CRPO record, use this script:

              Set Field [CR_Serial_Nos::CRPO_ID; CRPO::CRPO_ID]

               

              That step will create a new record in the related table and assign it a new unique CR serial number.

               

              You can do the same for PO's by creating a PO_Serial_Nos table for them.

              • 4. Re: Showing / Hiding Records
                cosmo
                  

                Thank you - you have been so kind to help me out.

                 

                I will try this.