No worries. You've already got me really far. I'll keep trying and let you know if I come up with anything. Thanks!
Original Message:
Sent: 09-04-2020 15:36
From: Brad Lemke
Subject: Creating reports that show only the columns where values are present
I can't say that I've ever actually tried to embed the variable grid-edit in a form. I was fortunate in that my solution only required two variants of two grids, so creating the 4 reports and only displaying what was relevant via dynamic form rules wasn't exactly brutal to do.
Not gonna lie, this particular request appears possible, but with quite a bit of work that I'm not very familiar with.
------------------------------
Brad Lemke
Original Message:
Sent: 09-04-2020 12:45
From: Jake R
Subject: Creating reports that show only the columns where values are present
Once again, thank you, Brad. This works great. A little embarrassing to have missed that obvious error, but really appreciate you pointing it out. Once again I have a follow-up question.
Do you have any thoughts on how this could be turned into an embedded grid edit report? I realize I'm being a bit greedy, but these solutions keep generating further requests as the report becomes used more and more.
I've tried replacing the iFrame reference with the new Grid edit query, but its essentially an iFrame to the Grid Edit report with all the QB headers and what-not. The larger issue is that once you save, the workflow breaks, and it's difficult to return to the original report. Using ifv= to remove headers has also been problematic. I've not been able to get it to work, but if I did it would remove the save button, which is also a problem.
I'm probably pushing my luck here, but you seem to have solutions to every problem. Curious if you have any idea.
------------------------------
Jake R
Original Message:
Sent: 08-28-2020 18:14
From: Brad Lemke
Subject: Creating reports that show only the columns where values are present
URLRoot () & "db/" & "bqehmzv89" & "?a=q&qid=22" & "query=?a=API_GenResultsTable&query=" & "{150.EX." & [Record ID#] & "}AND({'139'.XEX.'1'}AND({'102'.EX.'1'}OR{'105'.EX.'1'}OR{'127'.EX.'1'}))" & //The 150 in the above line is the Child record's reference field ID to the Parent. Adjust as needed/ "^clist="&"71.63.38."&[CList for Custom Report]&".177.178" & "^options=nfg.so-AAA.gb-V.nos.qws.sortorder-d^slist=71"
------------------------------
Brad Lemke
Original Message:
Sent: 08-28-2020 17:47
From: Jake R
Subject: Creating reports that show only the columns where values are present
These suggestions are awesome, though this one has stumped me. This is how I interpreted your suggestion:
URLRoot () & "db/" & "bqehmzv89" & "?a=q&qid=22" & "query=?a=API_GenResultsTable&query=" & "{150.EX." & [Record ID#] & "}AND({'139'.XEX.'1'}AND({'102'.EX.'1'}OR{'105'.EX.'1'}OR{'127'.EX.'1'}))" & //The 150 in the above line is the Child record's reference field ID to the Parent. Adjust as needed/ "^clist="&"71.63.38."&[CList for Custom Report]&".177.178" & "^options=nfg.so-AAA.gb-V.nos.qws.sortorder-d^slist=71"
Something in there is clearly wrong, as I'm landing on the child table's list all report and the URL string has a qid of -1000175
I'm curious if you're seeing something that's obviously wrong wit the syntax. Thanks!
------------------------------
Jake R
Original Message:
Sent: 08-28-2020 15:47
From: Brad Lemke
Subject: Creating reports that show only the columns where values are present
Hah, turns out this is kinda darn cool:
First, create a grid edit report in your child table (if it doesn't already have one). Columns/filters don't really matter. All you need is the report ID#.
Second, Create a formula URL field in the parent table, set it to open in new window, and add this formula:
- URLRoot () & "db/" & "child tableid" & "?a=q&qid=grideditreportID#" & ...
- Add everything that is "query=the rest of the formula" from your IFrame formula.
Try that out.
------------------------------
Brad Lemke
Original Message:
Sent: 08-28-2020 13:33
From: Jake R
Subject: Creating reports that show only the columns where values are present
In the grid-edit I do still need to hide all those unused fields, but going into grid edit in a pop-up is totally fine.
------------------------------
Jake R
Original Message:
Sent: 08-28-2020 11:48
From: Brad Lemke
Subject: Creating reports that show only the columns where values are present
Short answer: I have no idea how to do a dynamically generated grid-edit. If that api call exists, I haven't found it yet.
For the grid-edit mode, do you need to hide all of the unused fields? How do you feel about adding a link to your form that opens the full-grid in a popup?
------------------------------
Brad Lemke
Original Message:
Sent: 08-28-2020 11:08
From: Jake R
Subject: Creating reports that show only the columns where values are present
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
Original Message:
Sent: 07-14-2020 16:08
From: Brad Lemke
Subject: Creating reports that show only the columns where values are present
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
Original Message:
Sent: 07-14-2020 15:37
From: Jake R
Subject: Creating reports that show only the columns where values are present
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
Original Message:
Sent: 07-14-2020 10:28
From: Brad Lemke
Subject: Creating reports that show only the columns where values are present
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:
------------------------------
Brad Lemke
Original Message:
Sent: 07-13-2020 18:47
From: Jake R
Subject: 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 where only 10/40 fields have any information
Users are annoyed that they have to look at so many empty fields. Is there a way to display only the columns in a report if values are present?
For example, if the project has no value for "Rent" in any of the projects related "Bid" records, don't show the "Rent" Field in the project's embedded report.
I realize that we could achieve this by having multiple reports, however, we've come to the conclusion this would require maintaining over 200 reports, so we're looking for another solution.
Thanks!
------------------------------
Jake R
------------------------------