3 Replies Latest reply on Mar 16, 2017 3:16 AM by dale_allyn

    How can I script?

    jiang_aq

      For example:

       

      Company        PO#

      Nike                P001

      Nike                P001

      Nike                P001

      Adidas            P008

      Adidas            P008

      Puma              P034

      Puma              P034

       

      If I want to add one record, choose "Nike" as company name, PO# will be "P001" automatically

      if choose "Puma", PO# will be "P034"

      if add a new company, says "Apple", the po# will be "P035" incremented by 1 from "P034"

       

      How can I achieve it in simple way?

        • 1. Re: How can I script?
          wimdecorte

          The traditional way of doing this is to keep a separate table with one record per company and their associated PO number.

          Then when you add a record to your main table and you pick a company the PO field there would be an auto-enter calc that looks up (through a relationship on company) the PO #.

          This will then work without scripting.  You can obviously script it using the same relationship and not use an auto-enter calc on the PO # field but use a Set Field after grabbing the related PO # from the company table.

           

          This one needs more explanation:

          jiang_aq wrote:

           

          if add a new company, says "Apple", the po# will be "P035" incremented by 1 from "P034"

           

           

          Does it mean that each time you add an "Apple" record the PO # will increment by one from the previous?  In this case you'd need to write the PO # back to the company table each time you create a new record in your main table.  That is best done through scripting,

          • 2. Re: How can I script?
            dale_allyn

            Your description is a little vague, but I'll take a guess at what you wish to accomplish: I assume that the companies you mention are examples of customers or clients. If so, in your solution you would likely have a table called "Customers" or "Company" if you prefer. In this table you would have a number of appropriate fields: Company name, address, telephone, email address, website url, etc.

             

            You should also have a field that automatically enters a unique identifier (I do this for every table I build), e.g. companyUUID; it's a calculation field where the calculation is "Get (UUID)" (no quotes). This will automatically give each customer or company a globally unique identifier upon the creation of the company record. This unique identifier is my first field in every table.

             

            The next field I add is companyID (or similar). This field is a human-readable value such as your PO01, etc. If you wish to include the "P" I suggest that you set the field type to "Text" so that when or if you import records the "P" portion will be included (would need to know more about your usage to guide further). To create this ID field, add a field to your table, and under options choose the "Auto-Enter" tab; check the "Serial number" checkbox and add your value to the field labeled "next value". You can include alpha characters such as "P" or "P_" (no quotes) followed by your ID number (001, 002, 0036, etc.), so, P_035 would add that value to the next record you create.

             

            If you have more than one user who may be connected to the database at a time, you may want to choose "On commit" rather than "On creation". Again, we'd need to know more about your setup to guide.

             

            Use this "companyID" field on any layouts/reports where you need to see this value for a given company or customer.

            • 3. Re: How can I script?
              dale_allyn

              wimdecorte I was thinking that in this case "PO" is being used differently than traditional "Purchase Order". Since the OP is asking for it remain constant per company it looked like a company ID to me. I could be guessing incorrectly of course.