3 Replies Latest reply on Jan 20, 2009 12:26 PM by TSGal

    Problem W/ Script...  Please Help...



      Problem W/ Script...  Please Help...


      Good Morning Everyone.  Hopefully this is simple to do, but the more i try the more i get confuse.  Im trying to create a script that will do the following, but first let me tell you how i have this database so far.


      I have a Art Request Table, and a Products Table link via "ar id" field (auto serial # is the Art Request table)


      I also have a products Table, and a category table. (no links)


      Products Table contain        Category Table contain


      Products name                    Category Name

      Products code                     Category Code



      The process that i have so far is this.

      1-create art request

      2-art request is routed to Art Director

      3-Art director decides if a "New Product" has to be created

      or if a "Revision" to an existing product needs to be revised.


      If art director clicks on "New Product" i need the script to do the following (THIS IS WHERE IM REALLY STUCK)



           first 2 letters is the product line code

           the following letter is the Category code

           numerical value based on Product LIne and Category combined.

           so we have different product lines and categorys, so i need the database to know the last number it was created for that specific combination of Product LIne and category.



        a-new window will come up asking me to select product line and category Example  (CB=product line code) and then i select M for the Category code.  so we have CBM, at this point i need the script to search the entire Products Table or another one i don't know to see what the last Product Number that was created for this combination.  for example if the last onewas CBM0934-A,the this new one will be CBM0935-A.  THE "A" represents the first Product Number Version for this Number.


      **IF art director clicks "REVISION" then what i need the script to do is the follwing.

         a-again new window selects PRoduct line and Category, BUT after selecting product Line and category i need a field to show me all Product Numbers that match the product line and category.  So i would see a list of 50 or more product numbers with different Versions.  SO for example

      if i i have a list of 5 product Number.







      and i select  # 4  (CBM0349-B) then i want my script to create a new revision of that product. so it would create  CBM0349-D.


      SO the version is based on the alphabet.



      Please let me know if you have any ideas how to do this...




        • 1. Re: Problem W/ Script...  Please Help...



          Your description seems straight-forward, but it sounds like you are having trouble getting a Category code.  That is probably because you don't have any relationship set up between the Category table and any other table.  You will need to set this first on a "key" field that makes sense to you.


          Pull down the File menu and select "Manage -> Database".  Click on the Relationships tab and create the relationships between the Products table and Category table.  Also, between the Art Request table and Category Table, double click the icon in the middle between the two tables.  This will allow you to modify the relationship.  On one side is the Art Request table information, and the other side the Category table information.  At the bottom of the Category table information, put a check mark in "Sort Records" and select the field that shows the Product in Descending order.  That is, the most recent entry will be shown first.  When this is all set up, exit out until you return to the Layout/Browse mode.


          If you enter CBM0145, then in the portal, you should see CBM0145-B followed by CBM0145-A.  Although this is not necessary to create the script, it will show you the latest information entered for the Product code.


          Part of your script would contain something like the following:


          Set Variable [$product; Art Request::Product code & "-" & Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ";

                  Count (Products::Product code) + 1; 1) ]



          What this does is take the Art Request product code, add a hyphen, and then check to see how many occurrences are in the portal and add one.  If none, then the Count returns zero, add 1, and we'll take the first character of the string (A).  If there are three existing, we start with the fourth character (D).  Does that make sense?


          At this point, you can add a record to the portal and replace the code with the contents of the variable $product.


          This should get you pointed in the right direction.


          Let me know if you need clarification for any of the above steps.



          FileMaker, Inc.

          • 2. Re: Problem W/ Script...  Please Help...

            can't seem to make it work.


            Is there a way to send you a file so i can show you have i have?




            • 3. Re: Problem W/ Script...  Please Help...



              I have sent you a private message (top of this page - right side - X Messages) with instruction where to send the file.



              FileMaker, Inc.