Feed information from parent page to child

[dnorthrup]

I use a lot of Tables, Filters and Chart extension modules, and the one thing I am struggling with is how to feed info to a child page?

Let's say I have a Parent page that contains 10 clients, and I put a field called 'Status' on the table I am trying to leverage.

How can I (ideally bi-directionally, but either way) leverage the Parent table, in the child page?

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @dnorthrup ,

To pass information from the parent page to its child pages, we can suggest placing the master table with statuses wrapped into the Table Excerpt macro on this parent page.

Then on every child page you will place the Table Excerpt Include macro and select your Table Excerpt from the parent page using the Specific page -> Page Title option. So, every child page will contain the reused replica of the master table.

Then you may wrap every Table Excerpt Include macro into the Table Filter macro and show only the data that corresponds to this specific child page (filter by project names, employees, etc., hide unwanted columns).

But as usual we have the opposite case, I mean, when you have similar tables on the child pages (or pages with specific labels) and you need to collect them along with a source page meta data into one big report. This option is also can be achieved using our Table Excerpt/Table Excerpt Include macros. You may check here for an example: https://stiltsoft.com/blog/how-to-create-reports-from-multiple-confluence-tables/ 

[dnorthrup]

Thank you! I figured out the base concept of using the Excerpt table shortly after posting it. I think your link might answer my next Q (I have 2)

A) If I want to use SQL query and only return 'Customer Name = {Name of child page}' is there a way to do that? Either with a query or natural table functionality?

B) Leveraging the master table I am wanting to 'color code' a "Risk" column, is there a way to do that? I currently have it output "R" or "G" but I'd love for it to show the green /status or just make the cell green.

[Katerina Kovriga _Stiltsoft_]

So, for the first question please check the first part of this community thread https://community.atlassian.com/t5/Confluence-questions/Retrieve-text-only-from-table-cell/qaq-p/2124527

Here I show how to filter a table with the help of the Table Transformer macro: you may list not only one column, but list every column you want to show.

SELECT 'Date', 'Column 2', 'Column 3',
FROM T*
WHERE 'Phase' LIKE SELECT 'Date'
FROM T*
WHERE 'Phase' LIKE "%Development%"

Or there may be even an easier way - use the Table Filter macro, set the required filter and hide the filtration panel/filters in the headers:

1. Switch the page to the edit mode.
2. Select the macro and click Edit.
3. Switch to the Filters tab.
4. Click one of the following options on the right to select the filters display mode:
   • Filtration panel
   • Filters in header
   • Hidden filters <- select this option
5. Save the macro and the page.

Thus nobody can ruin your filtration from the page view mode.

What concerns the second question, you may use the Table Transformer macro for the so-called conditional formatting. This and this abstract should help you.

Note that if you need to wrap several macros one into another (for example, Table Excerpt Include or any other source table <- Table Filter <- Table Transformer macro), use the Table Toolbox macro that is a special shell for macro-wrapping in Cloud.

And if you are stuck with the query, please raise a support request with the screenshot of your original table - we'll recreate it and provide you with a required query.

[dnorthrup]

Thank you for the insight. I managed to get the dynamic rendering working perfectly. I will say the Filters piece isn't "quite" what I'm looking for.

Essentially as part of the process I want non-technical people to clone a template, and that template will auto fill `'WHERE 'Customer' = "{customerName}"` ; Customer Name will always be the title of the page, so I was hoping I could do something like 
WHERE 'Customer' = pageParams.name
or something adjacent. Worst case I can build in a step of them changing the filter, but was hoping for one button deploy.

[Katerina Kovriga _Stiltsoft_]

You may try to use the @PAGETITLE function (returns the page title and should be present in the Transformer's autocomplete) - seems that it is exactly what you need.

[dnorthrup]

Everything you've provided me so far has worked beautifully; thank you. I did have a weird issue today when I went back to make updates, the `FORMATWIKI` that was working yesterday seems broke today?

Even something as simple as

FORMATWIKI("{cell:bgColor=green}"+"{cell}") as 'On Budget',

is coming out normal, but was green yesterday. When I inspect it I see the HTML as

bgColor:green;textColor:yellow;align:center;

But no actual color change. Do you have any idea what may have changed?

[Katerina Kovriga _Stiltsoft_]

Hi @dnorthrup ,

I've managed to reproduce the issue and passed the request to our developers. Hope to notify you shortly about the results. Thank you for noticing and reaching out!

[Katerina Kovriga _Stiltsoft_]

Hi @dnorthrup ,

The fix has been just released but it may take 1-2 days to get to all the Cloud instances.

Sorry for the inconvenience!

[dnorthrup]

One last question (Love y'alls support so far BTW). Similar to @PAGETITLE, is there a page link option? I'd like every row that I am making link to the child page (Note I ended up reversing my use case and just maintain child pages that build the parent as you referenced in your original reply)

[Katerina Kovriga _Stiltsoft_]

The option to show page links is included into the Table Excerpt Include macro - go to the "Show page meta data" field and choose to show "Page" (it will be the page title with a link of the page from which you collect this specific part of the big table).

Also you may choose plenty of other information about your child pages to show in the final report.

[dnorthrup]

Perfect! Including metadata, changing query from 'Customer Name' to Page as "Customer Name" works beautifully. Thank you as always.

[dnorthrup]

One more Q - I'm 99% there.

If I'm making a template people are to clone to make their child pages; but I want the template to be excluded from the master - What is the best method for this? I tried using a Table Filter but the child page isn't showing in the dropdown. I want to avoid complicated SQL if I can (if name == "Template" type stuff) but if I have too, I will.

[Katerina Kovriga _Stiltsoft_]

Not sure how you create your new pages - have you created a space template and add the Create from template macro on the parent page?

Then you new pages will appear as child pages for the parent page and you won't see you template in the page tree.

[dnorthrup]

Thanks for reco - What I did in the meanwhile is filter out anything with 'Customer Name' (A field of the table) NULL. But you bring up a good best practice I should look into. We currently HAVE a child page called "<template>" people copy and replace. but Confluence templates might be better.

[Katerina Kovriga _Stiltsoft_]

Yes, try to look into Atlassian documentation regarding this point - such templates can also use instructional text (visible in the Edit mode to guide a user through the steps and invisible in the View mode). Besides your colleagues won't be able to delete the template accidentally.

To filter a table by an empty cell is also possible within the Table Filter macro. You may use two macros: the internal will leave non-empty cells (you set a reversed filter for an "empty" value and then hide the filtration panel) and the second external macro will be used by your colleagues for the filtration.

[dnorthrup]

One last Q for today - I think this might be an outlier.

IF('Current Target'>now(),
FORMATWIKI("{cell:bgColor=red|textColor=yellow|align=center}"+"{cell}")
, FORMATWIKI("{cell:bgColor=green|textColor=yellow|align=center}"+"{cell}")
) as "Past Due Date",

This is returning red when it should be yellow. As I run through test cases, I am THINKIn  it's because "26th" > "13th"

The Due Date is October 29th and October 31st - Yet it's returning Red. October 13th is not.

I am 'thinking' it's comparing the day, only. I have it set to treat timestamps as M, dd yy -- The /calendar output

[Katerina Kovriga _Stiltsoft_]

Yes, it looks like something is wrong with the date output.

Please, check this question about the date format  https://community.atlassian.com/t5/Confluence-questions/extracting-only-date-from-datetime/qaq-p/2145338

Then you may check what you receive by the now() function. All the three dates (current target, now () and the date format in the Table Transformer macro settings) should be in the same format.

Unfortunately, due to the time zones difference I'm not in the office to check it myself, but you may also try the "today" function instead of "now()".

Hope the hints will help - please let me know if the case works for you.

[Katerina Kovriga _Stiltsoft_]

Hi @dnorthrup ,

You may try this standard query from our documentation:

SELECT
CASE WHEN T1.'Month' < "today"
THEN FORMATWIKI("{cell:bgColor=red|textColor=yellow|align=center}"+ FORMATDATE(T1.'Month') + "{cell}")
ELSE FORMATWIKI("{cell:bgColor=green|textColor=yellow|align=center}"+ FORMATDATE(T1.'Month') + "{cell}")
END as 'Past Due Date'
FROM T1

Today is "27 Sep 2022" and the date format inside the Table Transformer macro is set to "d M yy".

Hope it helps your case.