LinkedIn
Twitter
Dear friends,
In my app I want to create a dynamic template whenever I make a checklist so that I can display only tthe filtered items in my ChecklisDetail table. Here is a sample of my table used to create the PDF template :
| ChecklistTemplateItemId | ItemName | VehicleType | Category | Code | Checked |
| T-ITEM207 | Feux | Camion Rigide | Trucks | A | ✅ |
| T-ITEM208 | Détresse | Camion Rigide | Trucks | A | ✅ |
| T-ITEM209 | Recul | Camion Rigide | Trucks | A | ✅ |
| T-ITEM210 | Rétroviseur | Camion Rigide | Trucks | A | ✅ |
| T-ITEM211 | Pare-brise - 20 cm | Camion Rigide | Trucks | B | ✅ |
| T-ITEM212 | Pneumatiques | Camion Rigide | Trucks | A | ✅ |
| T-ITEM213 | Goujons | Camion Rigide | Trucks | A | ❌ |
| T-ITEM214 | Fuites | Camion Rigide | Trucks | B | ✅ |
| T-ITEM215 | Ceinture de sécurité (3 points) | Camion Rigide | Trucks | A | ✅ |
| T-ITEM216 | Bâche | Camion Rigide | Trucks | A | ✅ |
| T-ITEM217 | Au moins une calle | Camion Rigide | Trucks | A | ✅ |
| T-ITEM218 | Marche pied | Camion Rigide | Trucks | A | ✅ |
| T-ITEM219 | Démarrage Moteur avec clé | Camion Rigide | Trucks | A | ✅ |
I want to display The items vertically like in my template as follows:
Currently I am using this code inside my google doc template:
<<Start: ORDERBY(FILTER("ChecklistDetail", [ChecklistID] = [_THISROW].[ChecklistID]), [ItemName])>>
<<[ItemName]>> | ||||||
<<[Image]>> | ||||||
<<[Checked]>> | ||||||
<<[Code]>> |
<<End>>
Your help is highly appreciated,
Best regards,
Solved
1 ACCEPTED SOLUTION
Here is something you could try.
In order to divide the filtered data into different groups, each of which corresponds to the individual column, you need to have sequential numbers attached to the data.
One way to do it is to copy the filtered rows' key column to a separate table, say table "Report", which enables it to use its [_RowNumber] for this purpose.
Then for each column of your template you can use an expression like
<<Start: SELECT(Report[id], MOD([_RowNumber], 6) = 2)>> ### = 2 should be changed to match the column
<<[id].[column of the original table]>> ### This is a dereference expression
...
<<End>>Below is an example template that I tested with. Make sure that you enclose each column in a separate table cell and each cell height should be the maximum size for alignment.
This is a sample report
Here is something you could try.
In order to divide the filtered data into different groups, each of which corresponds to the individual column, you need to have sequential numbers attached to the data.
One way to do it is to copy the filtered rows' key column to a separate table, say table "Report", which enables it to use its [_RowNumber] for this purpose.
Then for each column of your template you can use an expression like
<<Start: SELECT(Report[id], MOD([_RowNumber], 6) = 2)>> ### = 2 should be changed to match the column
<<[id].[column of the original table]>> ### This is a dereference expression
...
<<End>>Below is an example template that I tested with. Make sure that you enclose each column in a separate table cell and each cell height should be the maximum size for alignment.
This is a sample report
I have tried your suggested solution but it still does not work.
What I have done so far:
1- I created a table named Report: With id.rep as ref to DetailID in ChecklistDetail:
2- I copied the DetailID content in id.rep report:
And adjusted my template as follows:
<<Start: SELECT(Report[id.rep], MOD([_RowNumber], 3) = 2)>> | <<Start: SELECT(Report[id.rep], MOD([_RowNumber], 3) = 0)>> | <<Start: SELECT(Report[id.rep], MOD([_RowNumber], 3) = 1)>> |
Row:<<[_RowNumber]>> <<[id.rep].[DetailID]>> | Row:<<[_RowNumber]>> <<[id.rep].[DetailID]>> | Row:<<[_RowNumber]>> <<[id.rep].[DetailID]>> |
Image:<<[id.rep].[Image]>> | Image:<<[id.rep].[Image]>> | Image:<<[id.rep].[Image]>> |
<<[End]>> | <<[End]>> | <<[End]>> |
But the PDF file is not created.
Please try to help me figure out where the issue is .
Best regards,
Good morning,
After some tweaking I get it working though not on the form I want but I will try to adjust it until I get the desired form:
Here is what I have now as template:
and here is the output:
It still needs more adjustment as I have all the first page empty and images are not with the same size.What would you suggest as more adjustments?
I want to put 4 columns instead of 3 since the page fits.
Best regards,
One way to handle multiple users...
- Make the report table like
[id.rep] = UNIQUEID()
[ref.ssource] is a reference to the Data table
- Create an action on the data table that copies the selected row to the Report table and attache it to a place holder VC (app formula = ""). You will see a picture later in this post.
[req.seq] acts like [_RowNumber] now.
Set its behavior so that once copied to the Report table, the icon disappears.
- Also create a Report Control table like
You can create a slice of this table to only show the row corresponding to the logged on user.
- Create an action to run the report which modifies the [go.rep] to TRUE.
- Create a bot to run on changes to this Report Control table with a condition that [go.rep] is TRUE
- Then the template' START expression should be modified like
<<Start: SELECT(REP[id.rep], AND([rep.owner]=[_THISROW].[id.rep_ctrl],MOD([rep.seq],3)=1))>>
- On the Report Control table, you can create an action to delete the records on the report table and create another action to run both Run Report and this Delete at the same time.
- Create a dashboard to show a detail view of the Report Control Table's slice and the source Table
Here you can see (or not see rather) the rows that are already copied to the Report table.
Hope this helps.
This post is an extra.
If you are happy with the Google Doc template, then ignore this.
But as has been pointed out, Google Doc templates are not very flexible. Instead an html template can be used to render better formatted reports.
I attempted to do exactly that and will share it just FYI. I am no expert on CSS so do not ask me any questions. If you feel adventurous, go ahead and study it. Might be worthwhile you time and effort.
- The html doc created by a bot
- Print preview, note the page break position.
- The html template used, I am sure a lot can be tweaked to make it even better...
<html>
<head>
<style type="text/css">
@Page {
size: A4 portrait;
margin: 20px;
}
body {
margin: 0;
}
.page {
box-sizing: border-box;
width: 297mm;
height: 210mm;
margin: 0;
padding: 30px;
}
.record {
margin: 15px;
break-inside: avoid;
}
.cell {
margin: 0px;
padding: 2%;
border: solid;
border-width: 1px;
border-color: black;
block-size: 50px;
width: 20vw;
text-wrap: auto;
}
.size100 {
block-size: 100px;
}
.size200 {
block-size: 200px;
object-fit: contain;
}
.external_frame {
display: flex;
flex-wrap: wrap;
justify-content: left;
}
</style>
</head>
<body>
<div class="page">
<h1>Sample Report</h1>
<h2>Owner Information</h2>
<p>Report run by: <<[id.rep_ctrl]>></p>
<br />
<div class="external_frame">
<p>
<<Start:
SELECT(REP[id.rep],[rep.owner]=[_THISROW].[id.rep_ctrl])>>
</p>
<div class="record">
<p class="cell"><<[ref.ssource].[id.ssource]>></p>
<p class="cell"><<[ref.ssource].[sometext]>></p>
<p class="cell size100"><<[ref.ssource].[txt3]>></p>
<p class="cell size200">
<<[ref.ssource].[img.ssource]>>
</p>
</div>
<p><<End>></p>
</div>
</div>
</body>
</html>
As far as the google doc template is concerned , I am till now satisfied with its output.What matters most now is avoinding contradiction in using the Report table when multiple users are using the app especially at the point of copying and clearing the report content when generating the PDF file.I am really grateful for the explanation you have made above.My data table (source table) is displayed in a deck view with inline actions run from there and multiple users may run actions simultaneously.Does the solution serve in this case.
Here is a screenshot of my source table:
The action underlined is the one that creates the template.
Also , I have not understood how the adjustments you have made can avoid cross copying and clearing for multiple users even when I have only one table used to create the template.
Furthermore, each user copies all the rows related to his checklist from the source table (ChecklistDetail) and not only one row.This is a screenshot of my source table:
This is a child of Checklist table.
Concerning the action to copy items I have attached it to the bot where the items are first copied in ChecklistDetail from another table.This bot is triggered in the Checklist creation level:
So that the items are ready in the report before checking them in checklistDetail.
I can summarize my inquiry here in one question:Does using one table (Report) from different users to create different templates affect the pdf file creation given that each user has a different ChecklistID ,but may have the same DetailID(items)?
Witout applying your new appraoh, I have tested it from my phone and my laptop simultaneously , but when it comes to pdf file creation it took all the items regardless to which ChecklistID belong those items.So , I have to do something that makes the report differentiate between items based on the ChecklistID.So , instead of relying on a new table ,I should rely on the source table ChecklistID since each ChecklistID has its items.
Hope you have understood what I want.
This is two different users selecting his own set of rows to report. See the REP table and how the records are identified by the [rep.owner]. Also I0006 are selected by both users.
This is the status immediately after the second user pressed RUN and the completed rows are deleted. His action does not affect the first user in anyway because they work independently because of the owner field.
| User | Count |
|---|---|
| 18 | |
| 15 | |
| 10 | |
| 7 | |
| 4 |
