Forum Discussion

DaleMartin's avatar
DaleMartin
Qrew Member
6 years ago

Filter Reference Field Form selections and display result from another table

I have 5 tables with relationships built.   Tables are: Connectors, Cables, Cable Assemblies, Cable/Connector and Build/Price.

In the Build/Price Table I have a form where I would like users to be able to find existing cable assembly part numbers by selecting Cable Type (pulled from the Cable Table) then based on that selection, The form allows them to select each Connector  - Connector 1 and Connector 2 - which selections are limited depending on the cable chose. (relationship between the Build/Price and the Cable/Connector table filter the compatible cable and connectors).  Users also input a length.  This all functions properly and works great. 

The next step that I am trying to figure out is how to display an existing part number - that resides in the Cable Assemblies table - I am trying to use a Text-Formula field for this and use it to scan the Cable Assemblies table to determine matches to user input selections of Cable, Length, Connector 1 and Connector 2 and if a match exists to display that Cable Assembly Part Number Field.

I have built all relationships and am pulling over all of the necessary data between the Build/Price Table and the Cable Assemblies table but I can't get the formula to find a match.  It always responds with the "else" response.  It seems like the formula works immediately on the selection of the cable so and then doesn't find matches so it displays the "else" response.  Is there a way to delay the formula until after the 2nd connector is chosen?  Not sure if that would work or not.  Definitely need some help.

Here is the formula I am trying to use.  

If([Cable]=[Assembly - Cable Part Number]and[Length (in inches)]=[Assembly - Length (")]and ([Connector 1 Name]=[Assembly - Connector 1 Part Number]or [Connector 1 Name]=[Assembly - Connector 2 Part Number])and([Connector 2 Name]=[Assembly - Connector 2 Part Number]or[Connector 2 Name]=[Assembly - Connector 1 Part Number]),[Cable Assembly Part Number],"See Product Team for Quote")

In the attached screenshots, the TEST field is what I am trying to populate.  One of the screenshots shows the test field populated with "See Product Team for Quote")  that is my else if it doesn't find a match.  But notice, there have not yet been inputs or choices to Length, Connector 1 and Connector 2.

------------------------------
Dale Martin
------------------------------
Forms
Relationships
  • EverettPatterso's avatar
    EverettPatterso
    Qrew Captain
    6 years ago
    You only have one condition/one result in the formula, so it is working as you wrote it. 

    A traditional IF statement can return multiple conditions/results. 
    If(
Condition1, Result1,
Condition2, Result2,
Condition3, Result3)​


    If you want to make sure length, connectors, etc is filled in first, then add that as the first part of the logic. 

    If(
IsNull([Length]) or IsNull([Connector1]) or IsNull([Connector2]), "", 
Condition1, Result1,
Condition2, Result2,
Condition3, Result3)


    ------------------------------
    Everett Patterson
    ------------------------------
    • DaleMartin's avatar
      DaleMartin
      Qrew Member
      6 years ago
      Thanks Everett - 

      Here is the what I have now - 
      If([Cable]& ", " &[Length (in inches)]& ", " &[Connector 1 Name]& ", "&[Connector 2 Name]=[Related Assembly (ref) - Combo Name 3],[Related Assembly (ref) - Cable Assembly Part Number],[Cable]& ", " &[Length (in inches)]& ", " &[Connector 1 Name]& ", "&[Connector 2 Name]=[Related Assembly (ref) - Combo name],[Related Assembly (ref) - Cable Assembly Part Number])

      I am trying to solve for a condition where a user transposes the connectors.  The Cable Assembly part number remains the same no matter which connector is entered first.  In the Related Assembly Combo Name field I have the concatenated formula result that includes Cable, Length, Connector 1 and Connector 2.  I want to ensure that if a user enters the connectors in the reverse fields meaning the concatenated formula would then be Cable, Length, Connector 2 and Connector 1.  I have this lookup field as Combo Name 3.

      The formula works and displays the Related Cable Assembly Part Number if the user inputs the cable, length, connector 1, connector 2 but if the connectors are reversed, (which would match the Combo Name 3 field, the formula does not display the Related Cable Assembly Part Number.

      ------------------------------
      Dale Martin
      ------------------------------
      • AustinK's avatar
        AustinK
        Qrew Commander
        6 years ago
        Dale what do you have set in the field "Related Assembly (ref) - Combo Name 3"? You mention reversing the connectors but I'm not sure I follow as both parts of the formula have the connectors in the same order. Am I wrong in thinking you wanted this to be different? Perhaps closer to this?

        [Cable],[Length (in inches)],[Connector 2 Name],[Connector 1 Name]
        then
        [Cable],[Length (in inches)],[Connector 1 Name],[Connector 2 Name]

        Make sense?

        Also I think you could use the list field to help you out here, try this and let me know if it works for you.

        If(List(", ",[Cable],[Length (in inches)],[Connector 2 Name],[Connector 1 Name])=[Related Assembly (ref) - Combo name 3],[Related Assembly (ref) - Cable Assembly Part Number],
        List(", ",[Cable],[Length (in inches)],[Connector 1 Name],[Connector 2 Name])=[Related Assembly (ref) - Combo name],[Related Assembly (ref) - Cable Assembly Part Number])