8 Replies Latest reply on Aug 27, 2013 9:16 PM by JuneHiatt

    Restrict records in table

    JuneHiatt

      Title

      Restrict records in table

      Post

           I am coming to FMPro from Bento and new to setting up relational tables. I've searched everywhere but can't find an answer to what is probably a real newbie question; hope you can help.

           I have a file for Inventory and want to create two related tables. 

           1. A Work in Progress table that would show only the records with an entry in the Start Date field of the Inventory Table; the record should no longer appear in the Inventory Table.

           2. A Completed Projects table that would show only the records with an entry in the Finished Date field of the Work in Progress Table; the record should no longer appear in the Work in Progress table.

           Basically, I want to remove a record from one table and have it appear only in one of the others. I understand this can be done with Find and Omit, but I don't want to have to do that each time.

           So far the only solution I have found is to make an entirely different file and use export/import for each record as its status changes, and then delete it from the Inventory table, but this is less than desirable. Perhaps this has to do with setting up conditionals, but I couldn't puzzle it out.

           Thanks, DCB

        • 1. Re: Restrict records in table
          SteveMartino

               Some questions, thinking about your post:

               What happens to the records after they move?  Is this just so you can have a list of Work in Progress/Completed Projects

               I would consider 1 table for starters.  On layout trigger hides Completed records. Have a status field, with a value list or radio buttons to show Work in Progress or Completed Projects.  Have it select the proper one via a script trigger on Start Day field and End Date field.  If Start field has date and End Date is empty, change status to Work in Progress. If Start Date is populated and End date is populated, change status to Completed, hide record.

          • 2. Re: Restrict records in table
            JuneHiatt

                 I think what you are suggesting is just a different layout, not a different Table; is that correct?

                 Yes, I want separate layouts for Active Inventory, Work in Progress, and  Completed Projects. No record should appear in more than one list

                 I haven't tried scripts yet, but I get the general idea of an if/then field and will see if I can make that work tonight.

                 Appreciate the suggestion and thanks for taking an interest in this.

                 DCB

                  

            • 3. Re: Restrict records in table
              philmodjunk

                   I would not think in terms of two tables. Keep all your records in the same table, but bring up a different sub set of the total records. This could be the same layout or two different layouts. There are two basic approaches to do that:

                   1) Perform a find--this can be scripted and the onLayoutEnter trigger can perform it, to find all records that meet the work in progress criteria or the completed projects table. And script triggers can be added so that user performed find results are automatically limted to just one group or the other.

                   2) Set up a portal with either a relationship or a portal filter that limits the records listed in the portal to one or the other set of your records.

                   By keeping your records all in one table, you keep your options open for work with your records that requires working with both on the same layout such as a summary report that lists records from both sub sets grouped by whether they are "in progress" or "completed".

              • 4. Re: Restrict records in table
                JuneHiatt

                     Thanks for the suggestion. I'm beginning to think that perhaps a second file is actually more appropriate than different layouts or tables. The problem with keeping everything in one file is that once something moves to Work In Progress, the information is never needed in the Inventory again. I know how to save a Find and Omit a Record, etc.,  but I have to ask for it each time; there is really no need to have the two sets of records blended -- once the transition is made from Inventory to Work in Progress, the change is permanent. I want to see only what material can be worked with in the one, and what material is being worked on in the other. 

                     I know how to do this manually -- I export the record to a file in Finder, delete it from the original FM file, and then import it into a different FM file -- all the data in the record is transferred, so nothing is lost (and could be moved back, if necessary), but these steps are clumsy and time-consuming. I spent some time last night trying to learn how to write a script that would do this. It seems what I need is  a date trigger or button to initiate a script for it, but I'm doing something wrong because nothing I've tried has worked yet.  IIf you have a quick instruction for how to write it, I'd appreciate it -- it would save me a lot of time.

                      

                • 5. Re: Restrict records in table
                  philmodjunk

                       Separate files and separate tables are functionally the same thing but using separate files makes for a more complex implementation.

                       

                            The problem with keeping everything in one file is that once something moves to Work In Progress, the information is never needed in the Inventory again.

                       And why does that make keeping the data all in one table a problem? Why go to the trouble of moving this data at all? What problem does that solve for you?

                       

                            but these steps are clumsy and time-consuming.

                       And wholly unecessary if you keep all the data in the same table.

                  • 6. Re: Restrict records in table
                    JuneHiatt

                          

                         "Why go to the trouble of moving this data at all? What problem does that solve for you?"

                         When referring to inventory, I only want to see what is available to use for another project.  Records for Work in Progress or Completed are completely irrelevant for that purpose. Selecting a material from Inventory for a new project is one task. Monitoring a Work In Progress, or having a record of a completed project for reference is a completely different task -- they don't really relate or inform one another in any practical way once the material is taken out of inventory. The inventory contains unique items, not a flow of identical objects passing through.

                    A useful analogy might be antiques -- a chair is recorded in inventory when first acquired -- date, cost, source, photos, condition, etc;  When a client has the chair under consideration, the record is moved out of inventory to Pending and relevant data for that phase is added -- if no sale is made, it can be placed back in Inventory, When the item is Sold, details of the transaction are added and the final record then contains complete data of the entire progress of the item from first to last, including information on whoever had it under consideration but did not purchase it.. 

                         I know how to do a Find that selects for or omits fields with something like a Start date and how to save a Find, but that command has to be repeated every time, which is a nuisance in this case -- it would be easier to just export once and be done with it. But maybe there is something I don't understand about how to permanently hide certain records in one Layout but have them appear in another? 

                          

                    • 7. Re: Restrict records in table
                      philmodjunk
                           

                                When referring to inventory, I only want to see what is available to use for another project.

                           Understood, but this does not require separate tables to only see what is "available for another project". There are a number of ways that can control what records are visible/accessible on a given layout, you don't need separate tables to get that result.

                      • 8. Re: Restrict records in table
                        JuneHiatt

                             OK, clearly I've overlooked something obvious; I'lll go back and do some more homework . Thanks.