Exact forms

The one I'm trying to replicate.

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

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
------------------------------

Yep, these are the fields I want to show. 20.18.14.48.16.39.45

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

But they're not the ones showing on the display?  What columns are displaying?

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

Its still showing the default columns.

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

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

ohhhhhh.. shoot... let me try that and see if it works.

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

You're right. It showed the same records from the other one. Any new ideas?

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

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
------------------------------

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
------------------------------

Still gave me the default report - all entries from the child table, not specific for that parent table.

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

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
------------------------------

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
------------------------------

The first one worked fine and showed only project 260 on shipment 1, but the 2nd test showed all projects children again.

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

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
------------------------------

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
------------------------------

Let's break down the two queries: 

• first - query={'7'.EX.'260'}
  
  • '7' - is the field id of a particular field in the child table.  I'm betting it's named [Related Project].
  • .EX.  - is short for "EXactly".
  • '260' - is a specified value. 

This query pulled up all shipments for project 260, right?  That's because the query is saying to look for all child table records where Field 7 is exactly 260. 

• Second - query={'7'.EX.'"+ field["PARENTFIELDNAME"]+"'}
  • '7' - is still the field id of a particular field in the child table.  I'm betting it's named [Related Project].
  • .EX.  - is still short for "EXactly".
  • '"+ field["PARENTFIELDNAME"]+"' - is the specified value.  It's been changed from a static value (260) to a dynamic one (###) based on what the query finds when it looks for [ParentFieldName].

Try putting in the key field name (probably Record ID#) of the parent record.

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

Okay, that all makes sense, and yes, the key field for the parent table is Record ID#, and yes, that worked!!! Its showing just shipment #1. YAY!!!
​

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

Cheers!

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

Thanks so much Brad! I really appreciate you not giving up and continuing to try stuff until we got it to work. You're awesome!!!

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