Discussions

Expand all | Collapse all

Exact forms

  • 1.  Exact forms

    Posted 08-19-2020 10:57
    Hi, I'm needing some help with exact forms. I'm using the "Exact forms word template" from the QB help site and telling it to show a report link from field ~152~. However, its only showing default columns, and its not pulling over the filters I set up for that report. Is there a way to fix this? Thank you in advance!

    ------------------------------
    Megan Hardison
    ------------------------------


  • 2.  RE: Exact forms

    Posted 08-19-2020 19:20
    this thread should help
    https://community.quickbase.com/communities/community-home/digestviewer/viewthread?MessageKey=6a4098fd-be42-45db-974e-359ed279c617&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer#bm92b8f4db-e312-429a-b15a-5888dceea2f9

    ------------------------------
    Keith Jusas
    CEO
    Juiced Technologies, Inc.
    Ronkonkoma NY
    6316175060
    ------------------------------



  • 3.  RE: Exact forms

    Posted 08-20-2020 08:59
    Yes, I read that thread, but I don't understand it. I'm sorry.

    ------------------------------
    Megan Hardison
    ------------------------------



  • 4.  RE: Exact forms

    Posted 08-20-2020 10:01
    Edited by Brad Lemke 08-21-2020 12:25
    In Exact Forms, if you're linking to a report via field id (ex: ~132~) then it will only pull up the default report for the table that it's linking to.

    To do a full-fat version of the table report, you'll need to invoke an api instead of the field id.  The example query below may not suit your exact needs/purpose, but it will give you a solid foundation to build from.  If you copy and paste the code below into your exact form, replacing the 'bold' bits with the info specific to your app - it will work:

    ~=qdb.GetURL("childtable id","API_GenResultsTable^qid=childReportId#^query={'childfieldid#'.EX.'"+ field["parentfield name"]+"'}");~   

    A breakdown of how that works/where to find some of the info in your system:
    • ~=qdb.GetURL("childtable id",
      • You'll find this in the browser's address bar when navigating through records of that table.
    • "API_GenResultsTable
      • This is the API that you're invoking.  Information about how to customize what it pulls up is linked at the top of this post (which will also direct you to API_Doquery - they both follow the same rules/instruction set).
    • ^qid=childReportId#
      • You can find this number in a few different places.  The easiest would be to just open the report in your browser and it'll be the last bit of the webaddress.
        • Edit note: Including the QID of the report will override any customization/filters done through the next step, including filtering for just a parent record.
    • ^query={'childfieldid#'
      • This is the id# of the field relating the child records to the parent record.
    • .EX.
      • This means to 'find an exact match'
    • '"+ field["parentfield name"]+"'}");~
      • This is the field name of the field in the Parent table that the child field references to relate. 



    ------------------------------
    Brad Lemke
    ------------------------------



  • 5.  RE: Exact forms

    Posted 08-21-2020 09:36
    Brad, a few questions..
    1. Do I add this code in my Word document in place of where I had ~152~?
    2. For the "childtable id", do I just go into a record in the child table and copy the entire web address, or just part of it?
    3. For the "childReport id", is that just he number at the end of the web address?
    4. For the "childField id", is this the field named "related field" in the child table or is it ~152~?
    5. For the "parentField name" is this just the field name of the ~152~?

    ------------------------------
    Megan Hardison
    ------------------------------



  • 6.  RE: Exact forms

    Posted 08-21-2020 12:23

    1. Do I add this code in my Word document in place of where I had ~152~?
    2. For the "childtable id", do I just go into a record in the child table and copy the entire web address, or just part of it?
    3. For the "childReport id", is that just he number at the end of the web address?
    4. For the "childField id", is this the field named "related field" in the child table or is it ~152~?
    5. For the "parentField name" is this just the field name of the ~152~?
    Megan Hardison,  08-21-2020 09:35
    1. Yes, 100% replacement of ~152~
    2. It will be the part in bold: example.quickbase.com/db/childtableID?a=somemorehttpcode
      1. just the part after 'db/' and before the '?', if there is one.
    3. More on this later, but basically ignore this step -I misspoke and need to correct it-
    4. It is the field named 'Related field', but you're only putting the ID in that spot without the '~~'
    5. The actual spelled out version of the field that the "childField id" is pointing at/referencing in the parent table.

    In addition to all that stuff, I have an update:  referencing the 'qid="childreport ID" will override any additional filters/customization referenced in the 'query=' portion.  I wanted to use this as it would have drastically reduced the complexity of the second portion, but we really do have to go 'full-fat' query to display exactly the filters you want.

    Fortunately, the code that we need to write for this full query is available if you know how to find it:
    • If you have the 'related child records' table report displayed on the parent record's form, click on the "Full Report" at the top-left of that report.
      • This should have navigated you to a page that is just that report, filtered for that parent record. 
    • Click on 'more' at the top right of the page, and click on 'Show the expanded URL for this Report' (should be last option in the drop-down)
    • You'll get a message that gives you a whole bunch of code that'll look like this:
      • ?a=q&qt=tab&dvqid=-1000118&query={'7'.TV.'28'}AND({'59'.XEX.'1'})&clist=10.58.72.11.48.6.57.45.49.73.43.64.46&slist=41.40.74.27&opts=so-AAAA.gb-VV.nos.qws.
    • Copy and paste that code into here and I'll help you re-format it for the exact form query.


    ------------------------------
    Brad Lemke
    ------------------------------



  • 7.  RE: Exact forms

    Posted 08-21-2020 12:57
    ?a=q&qt=tab&dvqid=-1000060&query={'7'.TV.'260'}AND({'45'.EX.'1'})&clist=20.18.14.48.16.39.45&slist=45.39&opts=so-AA.nos.qws.&showDesc=1

    ------------------------------
    Megan Hardison
    ------------------------------



  • 8.  RE: Exact forms

    Posted 08-21-2020 13:17
    First, the cleaned up version of that code to copy/paste into the exact form:

    ~=qdb.GetURL("childtable id","API_GenResultsTable^query={'7'.EX.'"+field["parentfield name"]+"'}AND({'45'.EX.'1'})^clist=20.18.14.48.16.39.45^slist=45.39^options=so-AA.nos.qws.ned.nvw.nfg.phd");~

    • 'parentfield name' is exactly as we discussed before.
    • ned.nvw.nfg.phd is purely aesthetic/optional.  I assume that you're setting this up to print out, and adding these options will remove the 'pencils/eyeballs' as well as any new/updated flags.  Feel free to add/remove to suit your needs, or just to experiment to see what I'm talking about.
    • I also removed the 'showDesc=1' because I'm assuming that's the entry to display the description of the report.  I'm not sure how that'll show up on a GenresultsTable call, but you can add it back in using: ^showDesc=1
      • If nothing else, I'm curious to see if it comes up at all when referenced.  I may play around with it.
    Try this out and see how it looks.

    ------------------------------
    Brad Lemke
    ------------------------------



  • 9.  RE: Exact forms

    Posted 08-21-2020 14:08
    It didn't give me an error this time, and it showed a report, but its still not showing all the records (not using the filters I added for that report).

    ------------------------------
    Megan Hardison
    ------------------------------



  • 10.  RE: Exact forms

    Posted 08-21-2020 14:50
    So, the report that you pulled the code from earlier - did that have all of the filters you're talking about?

    What are fields 7 and 45 in that table?

    ------------------------------
    Brad Lemke
    ------------------------------



  • 11.  RE: Exact forms

    Posted 08-21-2020 15:15
    The earlier code always gave me an error, so I dont know.

    These are the fields in the child table:
    Field 7 is the related project.
    Field 45 is the Shipment #. (Its the last field on that report i want to print out if that helps)


    ------------------------------
    Megan Hardison
    ------------------------------



  • 12.  RE: Exact forms

    Posted 08-21-2020 15:26
    Are you trying to print/display all shipments for that project, or just one specific shipment? 

    I ask because the query code above says to only display records that are Shipment# 1  (query={'7'.EX.'"+field["parentfield name"]+"'}AND({'45'.EX.'1'})

    ------------------------------
    Brad Lemke
    ------------------------------



  • 13.  RE: Exact forms

    Posted 08-21-2020 15:42
    Just shipment #1. ​

    ------------------------------
    Megan Hardison
    ------------------------------



  • 14.  RE: Exact forms

    Posted 08-21-2020 15:45
    Its also still pulling all the default columns.

    ------------------------------
    Megan Hardison
    ------------------------------



  • 15.  RE: Exact forms

    Posted 08-21-2020 15:51
    hmm.

    When you went to get the extended url code, was that from the report you're trying to replicate in the Exact form, or was that the 'default report' for the child table?

    ------------------------------
    Brad Lemke
    ------------------------------



  • 16.  RE: Exact forms

    Posted 08-24-2020 08:08
    The one I'm trying to replicate.

    ------------------------------
    Megan Hardison
    ------------------------------



  • 17.  RE: Exact forms

    Posted 08-24-2020 09:03
    Alrighty, let's solve the columns issue first, then we'll work on the record filters:

    What fields are you trying to display as columns?  Do their field Id's match the part of the code listed below?

    ~=qdb.GetURL("childtable id","API_GenResultsTable^query={'7'.EX.'"+field["parentfield name"]+"'}AND({'45'.EX.'1'})^clist=20.18.14.48.16.39.45^slist=45.39^options=so-AA.nos.qws.ned.nvw.nfg.phd");~

    ------------------------------
    Brad Lemke
    ------------------------------



  • 18.  RE: Exact forms

    Posted 08-24-2020 09:50
    Yep, these are the fields I want to show. 20.18.14.48.16.39.45

    ------------------------------
    Megan Hardison
    ------------------------------



  • 19.  RE: Exact forms

    Posted 08-24-2020 11:54
    But they're not the ones showing on the display?  What columns are displaying?

    ------------------------------
    Brad Lemke
    ------------------------------



  • 20.  RE: Exact forms

    Posted 08-24-2020 12:31
    Its still showing the default columns.

    ------------------------------
    Megan Hardison
    ------------------------------



  • 21.  RE: Exact forms

    Posted 08-24-2020 12:50
    When you say default columns, what exactly do you mean?  Do you mean
    1. The default column list for the table, as in totally different columns/data from the clist?
      1. If so, check the query code for a"^qid=#" and delete it.
    2. Or the default column names, as in exactly the columns/data you listed in Clist but not the custom label specific to the report you're trying to replicate?
      1. If so, this is expected.  To have custom column labels in an exact forms genresults table API query is *possible* but requires a level of coding that I haven't even attempted.


    ------------------------------
    Brad Lemke
    ------------------------------



  • 22.  RE: Exact forms

    Posted 08-24-2020 13:56
    Its showing the default column list for the child table (the one I'm trying to pull the report from). What do I need to delete exactly? Sorry, just don't want to delete the wrong thing...

    ------------------------------
    Megan Hardison
    ------------------------------



  • 23.  RE: Exact forms

    Posted 08-25-2020 09:13
    OMG I think i got it to work!!! I went back to the original code you gave me and typed in a few options of what I thought you meant for the child report ID, and finally got it! So basically the part you thought you misspoke about, you were actually right. :-) Sorry this has been such a pain. So, this is what I used:

    ~=qdb.GetURL("CHILDTABLEID","API_GenResultsTable^qid=CHILDREPORTID^query={'CHILDFIELDID#'.EX.'"+ field["PARENTFIELDNAME"]+"'}");~ 

    What threw me off was that the child report id started with - and then a bunch of numbers. I just put the numbers in at first.
    So anyway, THANK YOU SO MUCH FOR YOUR HELP!!!! I really appreciate it!


    ------------------------------
    Megan Hardison
    ------------------------------



  • 24.  RE: Exact forms

    Posted 08-26-2020 09:18
    Have you found that this code works when you go to other records?  I found when using the "-1######" was initial success for that one record, but it wouldn't pull up the child records of another parent record.

    ------------------------------
    Brad Lemke
    ------------------------------



  • 25.  RE: Exact forms

    Posted 08-26-2020 15:10
    ohhhhhh.. shoot... let me try that and see if it works.

    ------------------------------
    Megan Hardison
    ------------------------------



  • 26.  RE: Exact forms

    Posted 08-27-2020 08:38
    You're right. It showed the same records from the other one. Any new ideas?

    ------------------------------
    Megan Hardison
    ------------------------------



  • 27.  RE: Exact forms

    Posted 08-27-2020 09:15
    Plan B is to use the 3rd party add on from Juiced. https://www.juicedtech.com/exact-forms-plus.  It generates an actual PDF or Word document and attaches it to the record.  The configuration interface is no code.  :)

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



  • 28.  RE: Exact forms

    Posted 08-27-2020 09:16
    Try this:

    ~=qdb.GetURL("
    CHILDTABLEID","API_GenResultsTable^query={'CHILDFIELDID#'.EX.'"+ field["PARENTFIELDNAME"]+"'}^clist=20.18.14.48.16.39.45^slist=45.39^options=so-AA.nos.qws.ned.nvw.nfg.phd");~

    ------------------------------
    Brad Lemke
    ------------------------------



  • 29.  RE: Exact forms

    Posted 08-27-2020 12:41
    Still gave me the default report - all entries from the child table, not specific for that parent table.

    ------------------------------
    Megan Hardison
    ------------------------------



  • 30.  RE: Exact forms

    Posted 08-27-2020 13:38
    When I had just the ~157~ on my exact form, it would show the default report, but only showed the child records for that parent record. If I can get it to show only shipment 1, I'd be good. I can work with it only showing the default columns, but I need it to filter out ones for shipment 1.


    ------------------------------
    Megan Hardison
    ------------------------------



  • 31.  RE: Exact forms

    Posted 08-27-2020 14:59
    Let's try this then, just to test and see if the query code itself is good:

    ~=qdb.GetURL("CHILDTABLEID","API_GenResultsTable^query={'7'.EX.'260'}AND({'45'.EX.'1'})^clist=20.18.14.48.16.39.45^slist=45.39^options=so-AA.nos.qws.ned.nvw.nfg.phd");~

    If this ends up working (as in it shows only project 260's children on shipment 1), then we have a working query.  If it works, let's expand from that one to this:


    ~=qdb.GetURL("CHILDTABLEID","API_GenResultsTable^query={'7'.EX.'"+ field["PARENTFIELDNAME"]+"'}AND({'45'.EX.'1'})^clist=20.18.14.48.16.39.45^slist=45.39^options=so-AA.nos.qws.ned.nvw.nfg.phd");~

    ------------------------------
    Brad Lemke
    ------------------------------



  • 32.  RE: Exact forms

    Posted 08-28-2020 09:51
    The first one worked fine and showed only project 260 on shipment 1, but the 2nd test showed all projects children again.

    ------------------------------
    Megan Hardison
    ------------------------------



  • 33.  RE: Exact forms

    Posted 08-28-2020 09:58
    Okay, now we're getting somewhere!

    There's either a Typo in the PARENTFIELDNAME (sometimes even missing capitalization can make the difference), or you're referencing a different field. 

    Is the parent field spelled [Record ID#] ?

    ------------------------------
    Brad Lemke
    ------------------------------



  • 34.  RE: Exact forms

    Posted 08-28-2020 10:35
    I double checked. no typo. Am I referencing the wrong field? The field I'm referencing is the field in the parent table that is a report from the child table showing everything from shipment 1. When I go into the field properties for that field, at the top it says the field label is "RentalInvShip1", so thats what I've been putting in the section PARENTFIELDNAME.

    ------------------------------
    Megan Hardison
    ------------------------------