Discussions

Expand all | Collapse all

Combined field for related fields?

  • 1.  Combined field for related fields?

    Posted 02-04-2020 13:55
    Many times when I have two different related fields pulling in information, if I want to display it more simply on a report, I will simply make a formula text field: totext([Field 1) & totext([Field 2]). 

    In this case, I have two related fields, lets call them Related Parts and Related Pieces. In each case, there will be only EITHER a related part or a related piece.

    Is it possible to make one field that will display which ever one it is, plus link to it? Essentially, doing what I showed above but not displaying as text - instead displaying as their name with link still live. 



    ------------------------------
    Mike Tamoush
    ------------------------------


  • 2.  RE: Combined field for related fields?

    Posted 02-04-2020 14:16
    Edited by Brandon Drake 02-04-2020 15:20
    Hi Mike, 

    The way that you can accomplish this is to create a formula Richt Text field.  You would create an if statement where the condition is what determines whether the [Related Parts] will be shown or [Related Pieces].  Then, as far as I know, you would need to create an anchor tag to create the link.  The following is an example:


    var text displayValue = if([Condition] = true, [Related Parts Proxy], [Related Pieces Proxy]);
    var number recordID = if([Same Condition as above] = true, [Related Parts], [Related Pieces]);
    var text tableID = if([Same Condition as above] = true, [_DBID_Parts], [_DBID_Pieces]);
    var text URL = URLRoot() & "db/" & $tableID & "?a=dr&rid=" & $recordID;

    "<a href='" & $URL & "'>" & $displayValue & "</a>"

    ------------------------------
    Brandon Drake
    JHI Group
    Monroeville OH
    ------------------------------



  • 3.  RE: Combined field for related fields?

    Posted 02-04-2020 18:59
    Thanks Brandon.

    One of my related fields uses the record ID for key, and is numeric. The other uses a related field that is not the record ID, and is a Unique key field that is text. How would I alter the above code to account for that?

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 4.  RE: Combined field for related fields?

    Posted 02-04-2020 19:13
    Mike
    When specifying a record in a URL you can specify the rid or the key

    &rid=
    &key=

    so just replace "&rid=" with "&key="


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Combined field for related fields?

    Posted 02-04-2020 20:50
    Gotcha - I didn't actually even make it that far, the second line creates var number except one of my related fields is non numeric. I get an error says it is expecting numeric but receiving text

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 6.  RE: Combined field for related fields?

    Posted 02-04-2020 21:12
    .. not sure if that is a question, but if so we would need to see the formula and the error message

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Combined field for related fields?

    Posted 02-05-2020 07:39
    Hi Mike, 

    I think that all you would need to do is move the if statement to get the record ID from the variable and put it directly in the URL variable.  This way, you will be able to place any data type in the formula.  The issue is with declaring a variable, it can only be one data type, but if you put it in the URL you will not need to declare a specific type. 

    var text URL = URLRoot() & "db/" & $tableID & "?a=dr&key=" & URLEncode(if([Condition to select parts or pieces] = true, [Related Parts], [Related Pieces])); 

    It is a good practice to URLEncode() your field if you have a primary key that is not the default numeric record ID just to ensure that if the primary key has any special characters you will not throw an error.  

    Let me know if this works.

    ------------------------------
    Brandon Drake
    JHI Group
    Monroeville OH
    ------------------------------