Add Auto-Increment Number to output doc Template

[AndyYang]

Hi people,

I have had a try on this popular tutorial by the intelligent contributor @Jonathon:

Workflow pagination, headers and footers Tips & Tricks 💡

Hey team, As of now, AppSheet does not officially support pagination for workflow pdf reports. This means that there is no native ability to include headers and footers, to number pages, or to format tables neatly that may run across multiple sheets. I spent some time this weekend setting up a system for workflow report pagination for one of my applications and figured I could pass on what I’ve learnt… Note that I have done all my testing with word document templates; I have not tested this wi…

However, my use case is a little bit different. There is no preferred number of rows to be displayed in one single page.

This is what I want to achieve:
Instead of #89 , #90 (_Row Number), I want it to show Photo 1, Photo 2, Photo 3 as images in the list getting populated to the output pdf.

Ultimately, I need simply an integer list along with the Site Inspection Photo. Every time a new report is generated, it will re-start the counting from Photo 1, Photo 2, Photo 3… etc.

This report is working against data from Site Inspection TABLE which contains a sub-table (/list) called Site Inspection Photos with Ref and Key.

Therefore, anything need to be worked around with this Site Inspection TABLE.

Is there any good advice?

[Suvrutt_Gurjar]

You may wish to take a look at the post thread below that was discussed yesterday only.

If you still face issues and if you post your template expressions, community could possibly help.

Random number on the report ( pdf ) Questions

Hello… Today i have create report on my apps. In myapps editor, number sort from smalles to large ( 1…3 ) [Appsheet editor] But when i create report, the number is random… [Report] the formula is [expression] how to make report with number not random ( same with apps editor ) thank you Deddy

[AndyYang]

Hi @Suvrutt_Gurjar

This is my current app: https://www.appsheet.com/Template/AppDef?appName=ProjectsMonitoringTEST-4779204

In the output report task - we already have got a set template.

We would like to add Photo Index (1,2,3,4…) in order to easily keep track of the photos taken. And the numbering will restart for each new report. (p.s. _Row Number cannot realise this) Normally one report will not contain more than 50 photos.

e.g.

Cheers

[Suvrutt_Gurjar]

Hi @AndyYang ,

May we request you to post the template expressions that you have in place?

The community, in general, helps at the expression level, and anyway the app you have shared is not opening up. Could you share the template expressions?

[AndyYang]

Hi @Suvrutt_Gurjar
Sorry I should have included the link to my template.

Here is it: Dropbox - Make report_AttachmentTemplate_20211209_214645.docx - Simplify your life

[AndyYang]

Example output as attached.
AppSheetDoc20211209_220655_505.pdf (924.4 KB)

[Suvrutt_Gurjar]

Thank you. Could you mention what is the key for child table “Site Inspection Photos” where the photos are stored. Also is the key fixed length, meaning 8 characters long or something similar or otherwise?

Assuming “Site Inspection Photos” table’s key is fixed length and with UNIQUEID(), please try the following expression at the end of your template

<<Start: [Site Inspection Photos]>>
<<[Photo]>>
<<[Timestamp]>><<[Description]>>
<<CONCATENATE("Photo ",(FLOOR(FIND([Key Column of Site Inspection Photos],[Site Inspection Photos])/11)+1))>>
<< End >>

If the key is different, please evaluate if it can be UNIQUEID(). Or else, please post back what is the key. If the key is not UNIQUEID(), the template expression will need a change.

[AndyYang]

Suvrutt_Gurjar:

<<CONCATENATE("Photo ",(FLOOR(FIND([Key Column of Site Inspection Photos],[Site Inspection Photos])/11)+1))>>

Thank you @Suvrutt_Gurjar for the help and attempt.

I tried the expression you wrote, it returned 1, 4, 7, 10, 13, 16, 19…etc

(screenshot)

[Suvrutt_Gurjar]

Could you please respond on this?

Suvrutt_Gurjar:

Assuming “Site Inspection Photos” table’s key is fixed length and with UNIQUEID() , please try the following expression at the end of your template

[AndyYang]

The original system Key generated are in fixed length. But I did change its expression to be readable because I want to set custom IMG filename.(discussed earlier last month)

The final outcome of the custom Key - will be kept uniqueness by timestamp down to second ‘’ when photo is taken.

Here are the values stored:

Initial Value Expression:
CONCATENATE([Site Inspection].[Job No.] ,"-“, [Site Inspection].[CMR Reference] ,”-", NOW() )

[Suvrutt_Gurjar · Accepted solution]

Oh okay. One reason I had bolded the presumption before sharing the expression was that Earlier expression expected fixed length, one word key.

Anyway for the latest key, can you please try the below expression? Please note the use of ref column and key columns.

<<Start: [Site Inspection Photos]>>
<<[Photo]>>
<<[Timestamp]>><<[Description]>>
<<CONCATENATE( "Photo “, COUNT(SPLIT(LEFT([Ref Column in Site Inspection Photos].[Site Inspection Photos],FIND([Key Column of Site Inspection Photos],[Ref Column in Site Inspection Photos].[Site Inspection Photos])),” , ")))>>

<< END>>

[AndyYang]

@Suvrutt_Gurjar This got it working! Thank you very much! So intelligent !

If you have a spare moment, would you please explain what this expression does?
COUNT( SPLIT(LEFT([ Ref Column in Site Inspection Photos] .[Site Inspection Photos], FIND([Key Column of Site Inspection Photos], [Ref Column in Site Inspection Photos].[Site Inspection Photos]) )

[Suvrutt_Gurjar]

@AndyYang , good to know it works per requirement.

Please take a look at the below description

COUNT(
            SPLIT(
                       LEFT([ Ref Column in Site Inspection Photos] .[Site Inspection Photos],
                                 FIND([Key Column of Site Inspection Photos],[Ref Column in Site Inspection 
                                 Photos].[Site Inspection Photos]
                                           )
                                  ),” , "
                       )

               )

A) [ Ref Column in Site Inspection Photos] .[Site Inspection Photos] gets a list of keys of all “Site Inspection Photos” related to the current parent record.
e.g. LIST(Key-1, Key-2, Key-3, Key-n)

B) FIND([Key Column of Site Inspection Photos],[Ref Column in Site Inspection
Photos].[Site Inspection Photos]) gets the number at which the current photo record key is located in the text of list key records constructed in A) above.
So if the current key is Key-3, FIND() will return 17 the position at which key starts in the list in A)

C) LEFT([ Ref Column in Site Inspection Photos] .[Site Inspection Photos],
FIND([Key Column of Site Inspection Photos],[Ref Column in Site Inspection
Photos].[Site Inspection Photos]
)
The LEFT() function combined with FIND() retains only the string of text in A) above upto current key.
So in the current example, it returns “Key-1, Key-2, K”

D) Due to use of LEFT() and FIND() , the LIST of keys has become a text string now instead of a list. So SPLIT() converts it back to the list.
SPLIT(
LEFT([ Ref Column in Site Inspection Photos] .[Site Inspection Photos],
FIND([Key Column of Site Inspection Photos],[Ref Column in Site Inspection
Photos].[Site Inspection Photos]
)
),” , "
)

So in the current example the text “Key-1, Key-2, K” is convrted back to LIST(Key-1, Key-2, K)

E) COUNT() counts the number of elements in the list and that is nothing but the photo serial number in the current list.

So in the current example, COUNT() returns 3 from COUNT( LIST(Key-1, Key-2, K))

Hope this helps.

[Kunal_Jain]

Thanks, this method worked for my need to generate the serial number for each invoice line item.

Ingenious!

[Suvrutt_Gurjar]

Thank you for the update and you are welcome. Glad to know you the solution worked well for your use case as well.

[SkrOYC]

Btw @Suvrutt_Gurjar, this works like this INDEXOF() tip by @Steve, right?

INDEXOF() - Google Cloud Community

[Suvrutt_Gurjar]

Hi @SkrOYC ,

Yes. More or less the concept is of indexing. However just for academic purpose, the suggestion by me in this post thread in Dec 2021 is about numbering all the rows in a report based on key values. So the index keeps moving to number the records one after the other.

In the  tip of @Steve that you have shared , @Steve has guided in the post in Feb 2022 on a much compact approach on INDEXOF() to find an element's index in a list. There have been a few more tips on INDEXOF() in the Tips and Tricks section and @Steve 's expression is most compact one.