Forum Discussion

AaronAlpert1's avatar
AaronAlpert1
Qrew Member
20 days ago

Report link - target field value is contained in

I have Table A and Table B. I would like to create a Report Link in Table A, which returns multiple records from Table B.

In Table A, I have a text field called AllDataPoints with value:

189-261 ; 189-21 ; 189-101

(It was originally a summary field of combined text, i.e., multi-select text, so I can play around with the delimiters and such.)

Table B has a field called MatchID (text). I want the Report Link to return 3 records from Table B -- the record with MatchID equal to 189-261, the record with MatchID equal to  189-21, and the record with MatchID equal to 189-101. In other words, I want records where MatchID is contained within AllDataPoints.

I tried changing Value Matching to contains and wildcard. My guess is that it would work if values in A were substrings of values in B, but I want it the other way around -- return records where B is a substring of A.

Any ideas? Thanks.

Report Link

3 Replies

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    20 days ago

    I think it only works in one direction and you want it to work backwards. 

    One solution would be to parse out the values in that string into separate fields and make multiple report link fields. But that would display pretty ugly on the form and wouldn't scale well if there were more children. 

    A solution would be to implement the user focus method for which I put an App in the exchange  

    The workflow would be that the user would push a button on the record. It would then put that record in focus for the user and the string from table A would get looked up down to all records in table B. Then on table B you would have a regular formula which would determine if it was a match and then you would have an embedded report on the table A record, which would only show once the button had been pushed, and that would show the matches.   

    Feel free to post back with any questions if you're interested in a method which solves the problem by having the user push a formula URL button. 

  • AaronAlpert1's avatar
    AaronAlpert1
    Qrew Member
    12 days ago

    Thanks, Mark!

    Here's how I actually solved it...

    I created a Formula URL as follows

    "https://etagen.quickbase.com/nav/app/[APP ID]/table/[TABLE B ID]/action/q?qid=20&query=" &

"{85.EX." &
SearchAndReplace(ToText([Concatenated MatchID]),
" ; ",
"}OR{85.EX.") &
"})"

    Basically, it took my list of ID, converted it to text (which automatically separates values with semi-colons), searched and replaced the semi-colons with the central part of a query. This produces a URL with query terms like {85.EX.189-261}, which means field ID 85 (that's where the MatchID field is in Table B) to one of the values in the Table A list (that's what all the ORs are about).

    Unfortunately, the user has to click the link to see the table. While API_GenResultsTable can normally generate the table directly in a webpage, QB's rich text fields don't allow JavaScript, so that didn't work. No big deal.