Ever wanted to make your exact form load conditionally?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Ever wanted to make parts of your exact form conditional based on what is in the record or even if there is data in a related table (which you have a summary report for)?

Photo of Jack

Jack, Champion

  • 50 Points

Posted 5 years ago

  • 0
  • 1
Photo of Jack

Jack, Champion

  • 50 Points
Here’s how!



If you have already created an exact form and uploaded it to Quickbase using the save function in the exact form template, then the page should be available under pages in the app settings.



If you haven’t then follow the instructions here https://www.quickbase.com/help/default.html#creating_exact_forms.html to create the basics of your form then read below.



For this example you will need a related table and a query string this can more or less be obtained from the expanded url from a summary report (on your form page, click on more, then expanded url) and your table ID (for example hfjfwhbn5).

You will also need to create a summary field in the table to table relationships that counts the number of related records in the table, this is used as the filter, which enables us to not display the table if it is blank table.



If you click on the page under the app settings you will be presented with a mixture of html and CSS.



If you scroll down below where the code contains: <div class=WordSection1> and:



For a conditional Field:

~=if(field["# of Record ID#"]!=="0"){field["My Name"]};~

This section looks at the filter ~=if(field["# of Record ID#"]!=="0"), so if the filter field is not equal to 0 then {field["My Name"]};~ show field [My Name].

The best part about this code is if the field isn’t displayed as it is equal to zero the space is collapsed giving the perfect exact form without blank spaces.



For a conditional Table:

~=if(field["# of Record ID#"]!=="0"){qdb.GetURL("hfjfwhbn5","API_GenResultsTable^query={'10'.EX.'"+ field["Record ID#"]+"'}AND({'6'.EX.'John'})&clist=18.16&slist=19&options=sortorder-D.gb-X.nos");}~



This section looks at the filter ~=if(field["# of Record ID#"]!=="0"), so if the filter field is not equal to 0 then {qdb.GetURL("hfjfwhbn5", "API_GenResultsTable^query={'10'.EX.'"+ field["Record ID#"]+"'}AND({'6'.EX.'John'})&clist=18.16&slist=19&options=sortorder-D.gb-X.nos");}~ show my summary report. Note to preserve the sort function in the table report you should use the fuller ending for the query “options=sortorder-D.gb-X.nos” for high to low and “options=sortorder-A.gb-X.nos” for low to high (as the expanded url ending doesn’t do this).

Again as above the best part about this code is if the report isn’t displayed as it is equal to zero the space is collapsed giving the perfect exact form without blank spaces.



You could also use this approach to have the option of creating one form now for two tasks, or setting up various fields conditionally, so they are only displayed when they contain data.



I hope you find this useful!!!!
Photo of Christine

Christine

  • 0 Points
Could you go more in depth on the fields I need to input? Is the # of Record ID # from the master table and the Record ID# from the details table? Why is there an AND function in the query?
Photo of Jack

Jack, Champion

  • 50 Points
Dear Christine,

Sure, the # of record ID is in the master table, it's a summary field you can create in the table relationship which counts the number of related child records that relate to the parent. This is useful as a filter as you do not want to display your report if there are no related records.

The record ID is the record ID of the master table but the field id number will refer to the field ID in the child table known as "related xxxxx". The field that links the parent and child together.

I was using the section after the "and" as a filter for the related records to return only related records with the word john in them. This is useful if you have a category in your child records like pet type and your options are dogs or cats. If you only wanted to return dogs then you would change John to dog and make sure the number six is changed to the field ID of your drop down field. If you don't want to use the filter you can just leave the "and" section in but delete the word John but leave the '' in and it will return all results. If you later decide you want to filter your query it's easy to just write a word between the '' it's also worth noting if you are using a two word filter you have to format it as 'WORD+WORD' each space must have a + symbol in place of the space.

I hope this makes sense.

Jack
Photo of Christine

Christine

  • 0 Points
Yes, that did make sense! Thank you so much.

I also think I may be looking for a slightly different solution.
So in my understanding, the above code is using a query to filter the records of a master table and returning the results of the details table. Is there a way to take all records of the master table and filter the records of the details table based on a query?

For example, I have a master table of Assemblies, and I would like to print an exact form for each assembly with its corresponding details. The details would be a list of related models (held in the Models table) for that assembly. Using this code, I am able to get the embedded table to either show up or not show up depending on the query. Can I get the embedded table to always show up but only show the relevant records?

Let me know if this is a separate issue that I should ask the community. Thank you!

Christine
Photo of Jack

Jack, Champion

  • 50 Points
Not Quite. The above query is counting the number of related details records related to the master record and then returning those detail records (with an optional filter on the detail records) if the amount of returned results is greater than 0.

If you wanted to use this without this count filter you would simply use:

~=qdb.GetURL("hfjfwhbn5","API_GenResultsTable^query={'10'.EX.'"+ field["Record ID#"]+"'}AND({'6'.EX.''})&clist=18.16&slist=19&options=sortorder-D.gb-X.nos");~

By removing the word john and the if section of the query it will return all the details results that relate to your master.

If you have other tables that relate to the master and want to show those records too, then simply create a version of the query above for that too.

I hope this makes sense.
Photo of Michael

Michael

  • 0 Points
I just tried running this
~=if(field[“# of Down Hole Equipment”]!==”0”){qdb.GetURL("bg474wsd4", "API_GenResultsTable^query={'6'.EX.'"+ field["Quote ID#"]+"'}^clist=68.363.353.71.43.70^options=nvw.ned.phd.nfg.sortorder-A^slist=255");}~

came back with Unexpected Token illegal error
Photo of Jack

Jack, Champion

  • 50 Points
Hi Michael

Try the below version:

~=if(field["# of Down Hole Equipment"]!=="0"){qdb.GetURL("bg474wsd4", "API_GenResultsTable^query={'6'.EX.'"+ field["Quote ID#"]+"'}&clist=68.363.353.71.43.70&slist=255&options=sortorder-A.gb-X.nos");}~

Remember you must have application tokens turned off to use exact forms.
Photo of Michael

Michael

  • 0 Points
Yeah, I knew about tokens turns out I had a bad symbol once I fixed that everything worked fine.  Thanks for the response tho
Photo of Michael

Michael

  • 0 Points
Next question any idea how to add a row colorization based on certain criteria to this?
Photo of Jack

Jack, Champion

  • 50 Points
I haven't ever tried this but I just created a report and added the colourisation formula then looked at the expanded url

Formula:
If([Purchase Requisitions]>200,"red","")

End section of url:
&rcform=If(%5BPurchase+Requisitions%5D%3E200%2C%22red%22%2C%22%22)%0A

This might give you something to go on. Alternatively you could just use the report builder, build the colourisation and take the end section of the expanded url (you can find this under more and view expanded url and paste it into your query. Hope this helps.
Photo of Michael

Michael

  • 0 Points
Can you nest If's in exact forms?  I'm currently using the following to pull, format, and filter the appropriate records
~=if(field["# of Down Hole Equipment"]!=="0"){qdb.GetURL("bg474wsd4", "API_GenResultsTable^query={'6'.EX.'"+ field["Quote ID#"]+"'}^clist=68.363.353.71.43.70^options=nvw.ned.phd.nfg.sortorder-A^slist=255");}~

can I nest the the if statement or is there a way to add it to the qry like the sorting?  Any thoughts
Photo of Jack

Jack, Champion

  • 50 Points
Theoretically for the row colourisation you could do the following:

~=if(field["# of Down Hole Equipment"]!=="0"){qdb.GetURL("bg474wsd4", "API_GenResultsTable^query={'6'.EX.'"+ field["Quote ID#"]+"'}^clist=68.363.353.71.43.70^options=nvw.ned.phd.nfg.sortorder-A^slist=255&rcform=If(%5BPurchase+Requisitions%5D%3E200%2C%22red%22%2C%22%22)%0A");}~

You should be able to nest this.

If your looking to nest the entire report query, this should be theoretically possible too.
Photo of Michael

Michael

  • 0 Points
ok back to the URL idea, I ran with this
~=if(field["# of Down Hole Equipment"]!=="0"){ qdb.GetURL("bg474wsd4", "API_GenResultsTable^query={'6'.EX.'"+ field["Quote ID#"]+"'}^clist=68.363.353.71.43.70^options=nvw.ned.phd.nfg.sortorder-A^slist=255 &rcform=If+((%5BQuote+For%3A%5D%3D%22Well+Test%22+and+%5BConsumable%5D%3Dtrue)%2C%22%23FA5858%22%2CIf((%5BQuote+For%3A%5D%3D%22Lease%22+and+%5BConsumable%5D%3Dtrue)%2C%22%23FA5858%22%2C%22%22))%0A");}~

I get a report but there's no colorization.  Is there a setting or format in exact forms for background colors?
Photo of Jack

Jack, Champion

  • 50 Points
Try this

~=if(field["# of Down Hole Equipment"]!=="0"){ qdb.GetURL("bg474wsd4", "API_GenResultsTable^query={'6'.EX.'"+ field["Quote ID#"]+"'}^clist=68.363.353.71.43.70^options=nvw.ned.phd.nfg.sortorder-A^slist=255&rcform=If+((%5BQuote+For%3A%5D%3D%22Well+Test%22+and+%5BConsumable%5D%3Dtrue)%2C%22%23FA5858%22%2CIf((%5BQuote+For%3A%5D%3D%22Lease%22+and+%5BConsumable%5D%3Dtrue)%2C%22%23FA5858%22%2C%22%22))%0A");}~

You left a space in the query.

See if that resolves it
Photo of Michael

Michael

  • 0 Points
ok tried that, no change then tried this
~=if(field["# of Down Hole Equipment"]!=="0"){ qdb.GetURL("bg474wsd4", "API_GenResultsTable^query={'6'.EX.'"+ field["Quote ID#"]+"'}^clist=255.211.68.367.377.71.69.70.188&slist=255&opts=so-A.gb-X.nos.&rcform=If+((%5BQuote+For%3A%5D%3D%22Well+Test%22+and+%5BConsumable%5D%3Dtrue)%2C%22%23FA5858%22%2CIf((%5BQuote+For%3A%5D%3D%22Lease%22+and+%5BConsumable%5D%3Dtrue)%2C%22%23FA5858%22%2C%22%22))%0A");}~
still get the report but no row colorization
Photo of Jack

Jack, Champion

  • 50 Points
If you think its an issue with the query try calling your saved query ID rather than manual query above and see if that works. If it doesn't it may have something to do with the exact form.

~=if(field["# of Down Hole Equipment"]!=="0"){ qdb.GetURL("bg474wsd4", "API_GenResultsTable&qid=5&jht=1query={'6'.EX.'"+ field["Quote ID#"]+"'}^clist=255.211.68.367.377.71.69.70.188&slist=255&opts=so-A.gb-X.nos.&rcform=If+((%5BQuote+For%3A%5D%3D%22Well+Test%22+and+%5BConsumable%5D%3Dtrue)%2C%22%23FA5858%22%2CIf((%5BQuote+For%3A%5D%3D%22Lease%22+and+%5BConsumable%5D%3Dtrue)%2C%22%23FA5858%22%2C%22%22))%0A");}~
Try changing the qid number to the query number of your saved  coloured report in the above example.

No guaretee this will work but its worth a try. I'll try at the weekend and see if I can get this working and post an update.
Photo of Michael

Michael

  • 0 Points
OK so I tried
~=if(field["# of Down Hole Equipment"]!=="0"){ qdb.GetURL("bg474wsd4", "API_GenResultsTable&qid=59&jht=1query={'6'.EX.'"+ field["Quote ID#"]+"'}^clist=255.211.68.367.377.71.69.70.188&slist=255&opts=so-A.gb-X.nos.&rcform=If+((%5BQuote+For%3A%5D%3D%22Well+Test%22+and+%5BConsumable%5D%3Dtrue)%2C%22%23FA5858%22%2CIf((%5BQuote+For%3A%5D%3D%22Lease%22+and+%5BConsumable%5D%3Dtrue)%2C%22%23FA5858%22%2C%22%22))%0A");}~ with updated query number 59 but it pulled all records not just the once where the Quote ID# = Related record for my quote.  Not sure why
API_GenResultsTable&qid=59&jht=1query={'6'.EX.'"+ field["Quote ID#"] isnt filtering the records
Photo of Michael

Michael

  • 0 Points
ok how about this?  Field 220 "Consumable" is a checkbox that determines if a row is highlighted.  So maybe something like
~=if(field["# of Down Hole Equipment"]!=="0"){qdb.GetURL("bg474wsd4", "API_GenResultsTable^query={'6'.EX.'"+ field["Quote ID#"]+"'}^clist=68.363.353.71.43.70^options=nvw.ned.phd.nfg.sortorder-A^slist=255&rcform=If(%5BConsumable%5D%3D%22%0%22%2C%22red%22%2C%22%22)%0A");}~

Still shows the report but the colorization is not there.  Is the last if with the format just not running or am I inputting it wrong?
Photo of Jack

Jack, Champion

  • 50 Points
I have done some experimenting with this tonight as I was ill over the weekend and have come to the conclusion that this probably isn't possible within the exact form without the use of custom CSS/Javascript to format the table colours, most of the table formatting comes from a CSS template and javascript query. This seems to be not be able to pull through the row colourisation. I would recommend you post this as an independent question on the forum and while you wait for people to respond review my response to another post on customising exact form tables with CSS as this may give you some pointers on the custom CSS template https://quickbase-community.intuit.com/questions/923435-how-to-format-api_genresultstable?jump_to=answer_2109680 , you can also follow similar steps to copy the script query that the exact forms use and copy it into your own code page, which then means by pointing your exact form to your code page you can amend the code perhaps making it work. Its unfortunately something that would take a fair bit of time and knowledge of both CSS and Javascript something other community members may be better placed to assist you with. Sorry I couldnt resolve you issue on this occasion.
Photo of Michael

Michael

  • 0 Points
Thanks Jack, I had reached the same conclusion.
Photo of Juan

Juan

  • 480 Points 250 badge 2x thumb
Hi,

I'm having trouble understanding this part of the query:

AND({'6'.EX.'John'})

Could you explain?

Thanks in advance.
Photo of Jack

Jack, Champion

  • 50 Points
Hi. Sure.

It means field 6 is equal to John.

There are other query string operators available, see the query string operators list for details on the link below:

https://www.quickbase.com/api-guide/do_query.html