4 Replies Latest reply on Jul 8, 2012 8:00 AM by nella

    Scripting Value Lists

    nella

      I need some help.

      I created two fields. The first one is ‘Requested by’ and the second is’ Requested by email’.

      I created two corresponding value lists for these fields.

      First, the names of the people making the purchase order requests and second, their email addresses.

       

      The ‘Requested by’ field holds the name of the person who is making the purchase order request and the’ Requested by email’ field is the email of the person who is making the purchase order request.

      I would like to automate the ‘Requested by email’ field to automatically fill in the email address of the person making the purchase order request.

       

      So, in other words, I need the corresponding email address to automatically enter when the name in the ‘Requested by” field is selected.

      This email field will be used for sending a copy of the purchase order to the person who created the purchase order.

       

      Make sense?

        • 1. Re: Scripting Value Lists
          MattLeach

          One way to accomplish this could be to set the Requested by email field to be a calculation field using the Case function:

           

          Case (

           

          Requestedby = "John";

          john@domain.com;

           

          Requestedby = "Jane";

          jane@domain.com;

           

          ;

          )

           

          A downside to this is anytime you need to add or remove from the RequestedBy value list, you would need to edit the calculation to include, remove the person.

          • 2. Re: Scripting Value Lists

            Hi nella,

             

            If the person making the request is a staff member then I would suggest you use a Staff table - you really should have one anyway.  Here is a file showing how you might resolve your issue using a Staff table.  In relational design, the goal is to eliminate redundancy. 

             

            You do not really need the email address in your Purchase Orders table since you have it in your Staff table, so you can just place the email address from your Staff table directly on your layout (see the blue email field on the Purchase Order layout).

             

            If it is important to capture the Staff's email at the time the Purchase Order was created then you can add a field into your Purchase Order (like you had started) and then use a Lookup (or auto-enter calculation) to plant that staff's email into the PO.  Choice is yours. 

             

            Message was edited by: LaRetta - you need to sign into TechNet to download (and see) the file I've attached.

            • 3. Re: Scripting Value Lists
              BruceHerbach

              Hi,

               

              Assuming you have a relationship between the name and the e-mails address and both the Requested by fields are in the same table/record you can create a dependancy between the field by using a let statement.

               

              Formula for Requested by e-mail

              Let (

              nm = Reqestedby;  //  This creates the dependancy between fields.  FileMaker will do the rest.

              table::e-mailaddress)

               

              Anytime the Requested by field is updated,  the Requestedbye-mail field will get the new e-mail address.

               

              HTH

              Bruce

              • 4. Re: Scripting Value Lists
                nella

                Thank you Bruce, Matt and LaRetta.