Forum Discussion

DaleMartin's avatar
DaleMartin
Qrew Member
5 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 ...
Forms
Relationships
EverettPatterso's avatar
EverettPatterso
Qrew Captain
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
5 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
    5 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])