4 Replies Latest reply on May 9, 2012 6:35 PM by rounakjain

    filtering values in related value list

    rounakjain

      Title

      filtering values in related value list

      Post

      Here's my file:

      http://dl.dropbox.com/u/9738427/Slave.fmp12%202.zip

      I have set up a script which adds new item to Product List (its a value list) if it does not already exist when the user enters an item in the first field of the portal. Then I made the necessary changes to set up a similar script for adding new size to the Product Size List. It doesn't quite work. It fails to recognize sizes which already exist for that product and treats every entry as a new size. Kindly help.

        • 1. Re: filtering values in related value list
          philmodjunk

          This proved to be more of a challenge than I thought. After a great deal of testing, I determined that the conditional value list was returning zero values via the ValueListItems function. I could not find a reason for why it returns a null list in this circumstance, but did manage to craft an alternative approach that does work.

          I used this expression in the If step:

          IsEmpty ( FilterValues (List (Product 2::Product Size ); Line Table::Product Size ) )

          This uses the same relationship as the conditional value list but uses the list function to access the related values instead of using the valueListItems function

          Your script, however, has two additional issues.

          Set field [Line table::Product Name ; $NewProduct ]

          Should read:

          Set field [Line table::Product Name ; $ExistingProduct ]

          And if you edit a size field to add a size not in the the somewhere other than the last portal row, your script deletes the wrong portal row.

          Don't see why you need to delete a portal row and add a new one here....

          • 2. Re: filtering values in related value list
            rounakjain

            Your script, however, has two additional issues.

            Set field [Line table::Product Name ; $NewProduct ]

            Should read:

            Set field [Line table::Product Name ; $ExistingProduct ]

            >> yes i had corrected that later

            And if you edit a size field to add a size not in the the somewhere other than the last portal row, your script deletes the wrong portal row.

            >> yes, right

            Don't see why you need to delete a portal row and add a new one here....

            >> becuase, the portal does not generate a new product id for the just newly created product.

            I request you again to do this though you have done this before:

            Open the file:

            http://dl.dropbox.com/u/9738427/Slave.fmp12.zip

            Make a new record in "Invoice Entry" table. When you will reach the "LTProductRate" field in the Portal, you will see an error:

            This field cannot be modified until “LTCustomer ID” is given a valid value.

            It works properly for the next portal record. The above error is received only in the first portal row.

            This is also why I need to delete the portal row and add a new one.

            • 3. Re: filtering values in related value list
              philmodjunk

              Yet your script only enters the name, not the product ID. This is could look up the wrong ID as each product size has a different ID.

              The script can copy the new product record into a variable and update the existing record in the portal row with the new ID by copying it from the variable.

              • 4. Re: filtering values in related value list
                rounakjain

                you replied three minutes after my reply, so i guess you did not see the script which i am using in that file. Sorry, I should have mentioned that i have changed the script (The script is named "If ProductID is empty create new product"). It is now triggered when I enter the "LTProductQty" box. The user enters the product name and product size. The lookup for the product id then comes into picture and a value is entered in product id when product name and product size both are found in the same record in the product table. If it does not find anything, the script is triggered. I hope thats also a correct approach. The only problem is that i still have to delete and create the portal row. Sorry again for not mentioning the changes i made, i was just so annoyed with lookup not entering the LTCustomerID that i forgot to mention the changes.

                This was all done in the last file that i posted. The change i have made now after reading your line "And if you edit a size field to add a size not in the the somewhere other than the last portal row, your script deletes the wrong portal row." is that i set OnObjectValidate trigger to "LTProductSize" field which clears the product id field. That way if I make any edits that results in creating a new product record then that portal row will be deleted and new one will be created at the last row in the portal.