5 Replies Latest reply on Nov 23, 2009 7:46 AM by ninja

    First-time user needs help

    jrhody

      Title

      First-time user needs help

      Post

      New user creating my first database of products.  Some products are specifically for the public sector; some for private sector and some for non-profit sector.  Some public sector products are specifically for federal government, some for state government and some for local government.  Some products fit into multiple sectors.

       

      I have a checkbox field where the user will select the sectors the product is intended for (they may select more than one).  I have another field which generates a unique serial number.  I need for the serial number field to have a two-letter prefix which indicates the sector(s) the product is designed for.  For example:

       

      Product Serial Number/Used in Sector(s)

      PU1234567 Public 

      PR1234568 Private

      NP1234569 Non-Profit

      AS1234570 All Sectors

      PN1234571 Private and Non-Profit

      UN1234572 Public and Non-Profit

      PP1234573 Public and Private

       

      Any product serial number with a PU prefix indicates this product is for public sector organizations.

      Any product serial number with a PR prefix indicates this product is for private sector organizations.

      Any product serial number with a NP prefix indicates this product is for non-profit sector organizations. 

      Any product serial number with a AS prefix indicates this product is for organizations in all sectors

      Any product serial number with a PN prefix indicates this product is for private sector and non-profit sector organizations.

      Any product serial number with a UN prefix indicates this product is for public sector and non-profit sector organizations. 

      Any product serial number with a PP prefix indicates this product is for public sector and private sector organizations. 

       

      I need an auto-entry field for the product prefix, which will return one of seven values (PU, PR, NP, AS, PN, UN, or PP) depending on which sector checkboxes are selected.  

       

      I have searched and read everything I can find, but I cannot figure out how to make such an auto-entry field.  Advice anyone?

       

      Thanks in advance,

      Jim 

        • 1. Re: First-time user needs help
          ninja
            

          Howdy jrhody,

           

          I think you're looking for a calculation field set to

          " Left (ProdSerNumber ; 2) "

           

          This can be autoentry calculation if it will NEVER change (ie. the product will never go from Public to Public and NonProfit

          Or it can simply be a calculation field.

           

          Is this what you're after?

          • 2. Re: First-time user needs help
            comment_1
              

            Try something like:

             

             

            Let ( [
            pub = not IsEmpty ( FilterValues ( "Public" ; Sectors ) ) ;
            pvt = not IsEmpty ( FilterValues ( "Private" ; Sectors ) ) ;
            npo = not IsEmpty ( FilterValues ( "Non-Profit" ; Sectors ) )
            ] ;
            GetValue ( "PU¶PR¶PP¶NP¶UN¶PN¶AS" ; pub + 2*pvt + 4*npo )
            )
             

             

            BTW, I'd suggest you use a calculation field instead of auto-entry - see here



            • 3. Re: First-time user needs help
              jrhody
                

              Howdy Ninja and comment,

               

              Thanks for trying to help me.  I know it can be frustrating trying to help someone completely new to a product, process, etc.

               

              I'll try to describe my problem better.

               

              In a database, I have three fields that I am concerned with.  The first one (called the "sector" field) allows a user to specify what sector a product is intended for.  That field uses checkboxes and the user can select all that apply.  The available values in that field are:  Public, Private, and Non-Profit.  

               

              The second field generates a unique serial number for each product.  

               

              What I want is to add a two-character prefix for the serial number.  This prefix should indicate whether the product is available to (1) only public sector organizations (PU), (2) only private sector organizations (PR), (3) only non-profit sector organizations, (4) public- and private sector organizations (PP), (5) public and non-profit sector organizations (UN), (6) private and non-profit sector organizations (PN), or (6) public, private and non-profit sector organizations.

               

              If there is a way to include that in the serial number, that would be great, but I haven't figured out a way to do that.  So instead, I have created a third field which will contain ONLY the prefix.  Each product's unique identifier would consist of two fields:  the two-letter prefix plus the serial number.

               

              What I am looking for is for the prefix field to evaluate the sector field.  If the "public sector" checkbox is the only checkbox selected, it would return "PU."  If the "private sector" checkbox is the only checkbox selected, it would return "PR." If the "non-profit sector" checkbox is the only checkbox selected, it would return NP.  

               

              If both the "public sector" and "private sector" checkboxes are both selected, but the "non-profit sector" checkbox is not selected, it would return "PP." If both the "public sector" and "non-profit sector" checkboxes are both selected, but the "private sector" checkbox is not selected, it would return "UN."   If both the "public sector" and "private sector" checkboxes are both selected, but the "non-profit sector" checkbox is not selected, it would return "PP." If both the "private sector" and "non-profit sector" checkboxes are both selected, but the "public sector" checkbox is not selected, it would return "PN." 

               

              If all three sector checkboxes are selected, it would return "AS."

               

              Does that explain it better?

               

              Thanks again for your help.  

               

              Jim 

               

              • 4. Re: First-time user needs help
                comment_1
                  

                That's what I understood, and the calculation above returns the prefix, based on the checkbox field (which I called Sectors).

                 

                 

                If you like, you can eliminate a field by changing the last line to:

                 

                GetValue ( "PU¶PR¶PP¶NP¶UN¶PN¶AS" ; pub + 2*pvt + 4*npo ) & ProductID

                 

                where ProductID is the unique serial number field. This will return the complete string (prefix & serial).

                • 5. Re: First-time user needs help
                  ninja
                    

                  You might also consider making a radio button set with all of the available combination options.

                   

                  This would make the calc simpler, but would force the user to select a single, proper combo of sectors rather than FMP doing the combination function.  Either way is possible.

                   

                  Just a thought...