Forum Discussion

JakeRattner1's avatar
JakeRattner1
Qrew Cadet
5 years ago

Creating reports that show only the columns where values are present

Hi Everyone, We get bids from vendor on projects (RFPs). For each project we end up having different information submitted. As a result we often end up with an embedded table on the project record ...
Formulas and functions
reports and charts
BradLemke's avatar
BradLemke
Qrew Assistant Captain
5 years ago
How well-versed are you with API manipulation?  Have you done an Iframe Embed into a form before?

I could see there being a way to create an Iframe embedded 'Api_Genresultstable' where the clist (column list) is dynamically generated via formula.    

Here is the code I would start with in a Rich Text Formula Field in the Parent Record: 

Customize and manipulate to your needs, of course.  Then create a Report Link field that matches this rich text field exactly to this rich text field:

Lastly, add that field to your form and see if you like how it works.  


------------------------------
Brad Lemke
------------------------------
  • JakeRattner1's avatar
    JakeRattner1
    Qrew Cadet
    5 years ago
    Thanks Brad!  This is exactly what I'm looking for.  I'm not familiar with this technique, but I'll give it a go and let you know how it works out.

    ------------------------------
    Jake R
    ------------------------------
  • JakeRattner1's avatar
    JakeRattner1
    Qrew Cadet
    5 years ago
    Hey Brad, 

    Thanks again for your help.  I've recreated the Rich text field you showed in your screenshot.  Now I'm going to create the Report Link.  I'm a little confused about this part.  I think I need to create another rich text field in my Parent table.  Do I copy paste the same code into that field as well?  If yes, how would I get around the errors (the fields references will not work on the parent record)

    Thanks,
    Jake

    ------------------------------
    Jake R
    ------------------------------
    • BradLemke's avatar
      BradLemke
      Qrew Assistant Captain
      5 years ago
      Both the rich text and the report link fields are parent-table only.  When setting up the report link field, you're not pointing it at any other table, you're not telling it to go from 'point-a' to 'point-b' like usual - you're telling it to go from 'point-a' to 'point-a', the same field in the same table.

      ------------------------------
      Brad Lemke
      ------------------------------
      • JakeRattner1's avatar
        JakeRattner1
        Qrew Cadet
        5 years ago

        Sorry for the late reply. This worked remarkably well.  Thank you so much for all your help.  I've been able to get virtually all of the features I was looking for out of this technique.  

        One follow-up question, if you don't mind: When I use this approach I lose some of the controls available with your typical embedded reports such as the ability to great edit and the 'more' options including 'export as csv'.  

        Is there any way to bring these features into the iFrame?  I think a workaround for 'export as spreadsheet' could easily be a button that exports the report with an "=csv" argument attached, but I'm unsure of how I could replicate the ability to grid edit.  

        Thanks again for your help on this one!



        ------------------------------
        Jake R
        ------------------------------
  • ShaneMiller1's avatar
    ShaneMiller1
    Qrew Cadet
    2 years ago

    Hey! A few years late, but is this still possible? I tried it and when I went to save it said "

    There are one or more problems with your entry

    Only text, Quickbase-accepted functions, fields, variables, and certain HTML tags are allowed in this field. JavaScript is not supported.

    Is there a workaround to get a report that only shows columns which have data points on child records?



    ------------------------------
    Shane Miller
    ------------------------------

    • ChayceDuncan's avatar
      ChayceDuncan
      Qrew Captain
      2 years ago

      I skimmed the above - if you're trying the exact same technique as what's referenced you will get blocked - as Quickbase disabled any injection of javascript or iFrame type techniques in 2021 for security reasons. You can try a couple things though perhaps: 

      1. Try and generate the same 'API_GenResultsTable' similar to the above - and put that url into a Formula-URL field and enable it to show as an iframe. That is the only method that would allow you to use the iFrame technique. Essentially it's the same as the rich-text technique, you're just not actually putting the <iframe> tag in, just the URL string.

           2. The other option which might get hairy and very taxing on QB - if you're really just trying to control the columns, you could use 'formula-queries to actually check the fields that you want to show/hide. So you can check for example if fid 7 has values in it, and if it does, include it in your clist, if it doesn't leave it out. If you have 40 fields that will be very taxing on QB though so use with caution. 



      ------------------------------
      Chayce Duncan
      ------------------------------
      • ShaneMiller1's avatar
        ShaneMiller1
        Qrew Cadet
        2 years ago

        Thank you Chayce,

        You've given me enough information to be dangerous. I appreciate the quick response!



        ------------------------------
        Shane Miller
        ------------------------------