Open the report opened via a report-link field in grid edit mode

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

I have a report link on a form to a child table containing records related to the parent table. I need for the report that displays after users click on the report link field to open up in grid edit mode. For good reason I can't embed the report on the form and make it editable. Don't want to bore you with the details :)

Any help would be appreciated!

Thanks!

Liz

Photo of ECastellaw

ECastellaw

  • 20 Points

Posted 5 years ago

  • 1
  • 3
Photo of Mark_Shnier

Mark_Shnier

  • 640 Points 500 badge 2x thumb
no problem. We will use a combination of two techniques for a low tech solution.

1. Make a report called Child records for a parent which has an <ask the user> for the field [Related Parent]. Set up the report as showing to no Roles so that it does not get broken by accident. Run the report and answer the with anything and observe the URL. It will look like this h t t p s://mycompany.quickbase.com/db/xxxxxx?a=q&qid=23&nv=1&v0=123 (I have put spaces in the h t t p so the link does not get mangled by this forum software.

That link says to run the report with qid= 23 and the number of variables (nv) is 1 and the first variable (v0) is equal to 123



2. edit the URL and suffix it with &dlta=mog. Observe that it now is a grid edit report! I have no idea what that stands for (&dlta=mog) , but when you go into Grid edit the URL always reads that way, so "monkey see monkey do".

3. Now for the magic part - we want to build a URL formula field which will run the <ask the user> report, answer its own question as to who the [Related parent is] and for icing on the cake render in Grid edit.

Here we go


URLRoot() & "db/" & [_DBID_ CHILD_TABLE] &"?a=q&qid=xx&nv=1&v0=" & totext([related parent]) &"&dlta=mog"
Photo of ECastellaw

ECastellaw

  • 20 Points
Mark,

Thanks so much for the instructions!  I have the formula-URL field in the parent table and it definitely opens a grid edit report in the child table, but the Related Project field isn't preloaded.  Did I miss something?

Here's my formula:
URLRoot() & "db/" & "bijjd9xi5" &"?a=q&qid=13&nv=1&v0=" & ToText([Related Project]) &"&dlta=mog"


Thanks again!
Liz
Is the report that you created an <ask the user> report?  Does it ask the user for the value of the [Related parent]?
Photo of ECastellaw

ECastellaw

  • 20 Points
Yes, but there's a proxy field for the [Related Project] field.  Does that matter?
It should not matter as long as you are using the [Related Parent] in your URL.  What does the URL which gets generated look like? can you post that?  You can blank out your actual dbid table number if you like.
Photo of ECastellaw

ECastellaw

  • 20 Points
Photo of ECastellaw

ECastellaw

  • 20 Points
By the way, thanks so much for all your help on this!!
Photo of ECastellaw

ECastellaw

  • 20 Points
Further explanation of the app may be helpful.

I have three related tables with the following relationships:
1. Project to many Deliverables
2. Project to many Project Notes
3. Deliverables to many Project Notes

The related field for the third relationship is conditional on the Related Project being the same for the deliverable and project note.

The URL is for a report stemming from the second relationship.  I didn't think the other relationships would affect the report since project notes are related to projects and each project note has a value in the Related Project field.
OK, so the answer to the Question is a zero.  That cant be right.  You see where it says
&v0=0   ?  

That means that the value in the [Related parent field] is a zero.  You need to see what is really in that field.  can you also post the actual formula that you  are using and not just the result?
Thx
... in the report link setup field, there will be a field used for the report link report to know which records to display.  we are looking to create a report which will be doing an <ask the user> on that same field.
Photo of ECastellaw

ECastellaw

  • 20 Points
Here's my formula:
URLRoot() & "db/" & "bijjd9xi5" &"?a=q&qid=13&nv=1&v0=" & ToText([Related Project]) &"&dlta=mog"

I have a URL - formula field in the Projects record, but I don't have a report link field.
Photo of ECastellaw

ECastellaw

  • 20 Points
I got it! I changed ToText([Related Project]) to [Record ID#], which is the key field in the Projects table and now it works.  Thanks!!
Yey!  :)
Photo of ECastellaw

ECastellaw

  • 20 Points
Never mind. I ran into another issue.  The [Related Project] field isn't preloaded for new records.
How can it not be loaded for new records?  I've lost track of what table you are trying to display.  Is it the Project Notes table?
Photo of ECastellaw

ECastellaw

  • 20 Points
I created a formula - URL field in the Projects table that links to a grid edit report in the Projects Notes table.  The grid edit report automatically filters the Project Notes where the [Related Project] reference field in the Project Notes matches the [Record ID#] field for the Project form being viewed.  

So the grid edit report is filtered properly, but the [Related Project] column isn't preloaded.  Make sense?
Is the issue that you are trying to use grid edit to add records and the connection to the parent record is not being done in the grid edit report?
Photo of ECastellaw

ECastellaw

  • 20 Points
That's correct.
In my experience, when you have an embedded Grid edit report on a Parent record  and the [Related Parent field] is one of the columns, then the [Related Parent record ID#]  show up up in light grey meaning that the children will know who their parent is when the Parent is saved and they will not end up being orphans.

But if you leave the parent record it loses that intelligence - even if you take an embedded report and manually click Grid edit, the Add children loses its knowledge to default to the [Related Parent].  So we may be at a dead end.

Maybe we should circle back and explore "the long story" of why you cannot have the Grid edit report right on the parent in the first place.

If its an issue that the form is too complicated or whatever, why not have a button to redisplay the project record in edit mode on an alternate form which just has that grid editable child table and almost no other fields?
Photo of Meg

Meg

  • 0 Points
This is just what I needed - thank you.
:Nice to know my efforts on this forum have some lasting value :)