Forum Discussion

ConorGray's avatar
ConorGray
Qrew Member
8 months ago

IF GetFieldValue Formula?

Hello QB Pros! I am trying to set up a field that compares the entry of one field to a list in another field and gives a result based on whether the fields value is in that list.

Example. User enters a Part Number "12345" in the the part number field. 
Another field looks in another table for Part Number "12345" and returns the text "Out of Stock" or some other text.

I gather it is probable a combination of If and Get Field Values.

Thanks for your help!



------------------------------
Conor Gray
------------------------------

7 Replies

Sort By
  • DavidHyde's avatar
    DavidHyde
    Qrew Member
    8 months ago

    This is possible, 

    Are you trying to make it automatically count off of an inventory or are you manually managing inventory and you want to see what the status of an item is?

    Both are possible but one is a little more complex than the other.



    ------------------------------
    David Hyde
    ------------------------------
    • ConorGray's avatar
      ConorGray
      Qrew Member
      8 months ago

      Thank you David! Here is the exact problem I am trying to solve.

      We made an app that allows technicians to order parts. We have the complete inventory in a table that is related to the form the technicians are using. That's working really well.
      However, there is another table that has a list of parts that are "Restricted" which require additional approval/routing. 
      I want the technicians to order the parts like normal, but when the part number they request matches a part number on the restricted parts list, if flags it as "Restricted" and triggers a notification. 
      I hope that clarifies! Thank you!



      ------------------------------
      Conor Gray
      ------------------------------
      • DavidHyde's avatar
        DavidHyde
        Qrew Member
        8 months ago

        The easiest way for that would be to...

        Create a Text formula called [Status] in the Restricted Table that is simple like this. 

        Restricted

        Create a relationship from the Restricted Table to the Inventory Table based on part number. 

        Then create a lookup field to the Inventory Table for the formula field that you made called [Status]. 

        Then go to your Product Ordering Table and create a lookup field to your Inventory Table that pulls that same field you just pulled from the Restricted Table. 

        Then for some extra flair I would hide the lookup field and create another Rich text formula field that looks something like this. 

        If ([Inventory - Restricted - Status]="Restricted","<div class='' style='padding: 3px 5px; background: #FF9999; border-radius: 8px; text-align: center;'>Restricted</div>","<div class='' style='padding: 3px 5px; background: #99FF33; border-radius: 8px; text-align: center;'>Available</div>)

        Then place the new formula next to the part number in the order sheet and with the Dynamic Form Rules -  hide the field unless the field says "Restricted" or "Available" so that you don't have an ugly empty block sitting there until a part is selected. 

        If i misunderstood I apologize. I hope this helps!



        ------------------------------
        David Hyde
        ------------------------------