Columns on report template

[billjohnson]

Hello, I have a report template that I need to save as PDF with 2 columns after the title.
I’m using MS Word and tried to just click columns in the ribbon of the template document but no luck.
here’s what I have so far:

                        ` <<TODAY()>>`
                 Pelham Thursday Commissary Order
                        Friday Delivery

<<Start: Select(Thursday[_RowNumber], AND(ISNOTBLANK([Order]), [Order] > 0))>>
<<[Product]>>: <<[Order]>>
<<End>>

Example needed:
Product 1 : Order Product 3 : Order
Product 2 : Order Product 4 : Order

[WillowMobileSys]

billjohnson:

and tried to just click columns in the ribbon of the template document but no luck.

I’m not sure what this means and how it relates to AppSheet?

As for the template, it seems to look good except you don’t need the tick marks around the TODAY() function. It should just be <<TODAY()>>. Maybe that was just how you typed it into the post?

If you are trying to run this from a Workflow and its not generating, try looking at the Log entries for any errors. You can get the Log by expanding the Workflow and clicking on the Log button. Then scroll through the entries to find where your Workflow tried to execute. If there was an error, it will be colored in red. Click on the binoculars to see details.

[billjohnson]

Workflow is working correctly with no errors.
The issue is with the layout of the template.
I have 56 products with order quantities. So the saved PDF is 2 pages long. I get 36 products on 1st page and 20 products on 2nd page. I would like to condense the saved PDF report to only one page with 2 columns. Without changing the font size to super small.

[WillowMobileSys]

Got it! I know this has come up before and I’m pretty sure the answer was it couldn’t be done without some kind of data manipulation.

For example, introducing a table where the rows are simply used to help organize the report. In your case, the rows could have columns such as “Product A”, “Order A”, “Product B”, “Order B”. Then you can write two items with a single row.

Its cheesy I know. The issue is getting the template to process blocks (i.e. Start/End blocks) vertically rather than horizontally. Not to many vendors do it because of its complexity.

[WillowMobileSys]

This was bothering me so I decided to play around a bit with templates in Google Docs.

Google Docs do have the concept of text columns but I haven’t figured out yet if I can get a list in a Start/End block to spread across the columns - like normal typing does.

However, I have successfully created a two column table and was able to use two separate Start/End blocks - one for each side of the table. Below is a sample of the template and sample result in a report.

The thought here is that maybe you can break your Start/End rows into sections - one for rows 30 and less, a second for rows 31 to x, a third for rows x+1 to y, etc. Not ideal but the end report would be nice.

Sample from Template

Sample result from Report

[Aleksi]

As @WillowMobileSystems described, yes you can use nested tables with gDoc. You can write your Start: expression like <<Start: SELECT(Table[KeyColumn],MOD([NumberColumn],2)=1)>>. I have used this method and it works just fine.

[Koichi_Tsuji]

I suggest it is prudent that this tips should be documented somewhere in Appsheet knowledge base and/or support page.

[WillowMobileSys]

Yes, I had planned to add a Tip article once I have all the ducks in a row!

[WillowMobileSys]

Thanks for expression. Just to clarify, is [NumberColumn] a sequentially numbered column?

Also, the ideal would be NOT to have to break up the list into table at all. Google docs do support up to 3 text columns. So, for everyones understanding, if I define two columns and type into the left hand side/first column, entering lines of text, once at the end of the page the text entry will automatically shift to the top of the second column. Continuing entering lines of text and at the end of the second column, text entry will shift to the second PAGE, first column. (see sample image below)

Do you know why when a doc is generated from a template in AppSheet, it doesn’t honor the document defined columns? Is it removed somehow during preprocessing?

Sample of 2 column doc after entering 2 columns fist page and moved to second page

[Aleksi]

Well, it doesn’t need to be sequential number… but if you have just numbers like 1,3,5,9,13,17 etc, it will add values only to left column. Though _RowNumber is one option.

[Koichi_Tsuji]

@WillowMobileSystems
@Suvrutt_Gurjar

Expression or trick to get the index number Questions

Appsheet expression of INDEX(list, number) will return the ‘value’ of the designated index number out of the arrray/list. I’m now exploring a way to get the index number out of the expression. For instance, we construct the expression to generate list by using select function and so on. In a sense, the list of those rows are kind of the array. I wish to have the expression to get index number for each rows generated dynamically by list expression. Anyone does know if this is possible or we …

Now we found a way to dynamically to place the sequential number to the LIST. See the post above.

Based on the tricks and tips here, we are now able to create the multi column email template.
I m wondering now we should be able to create the two (or three or more?) column template out of SINGLE Table.
I m not testing this idea yet, not sure if it would work or not.

I received some claims and request from my client to split the list on the template into two column.
For instance, I do have table contains bunch of images. Now what i can do is to render each image one by one to row in table style in template. This sometime kills the space of the final output PDF, so it is quite understandable that my users of the app wish to see the table with two column style.

Using the trick i leaned from @Suvrutt_Gurjar, we are now able to split the single list or table or slice into two. For the select expression, just place the expression to differentiate the sequential number which is dynamically created by the expression and then assess if the value is even or odd.
If off, then place to the right handsite of the email template. IF not, place to the left (second column).

When i could get a chance and time, i will test this idea, to see if it would work, i.e to display the single list and split into two to get the two column style output on the template/output.

[WillowMobileSys]

I don’t want to stray to far off the original poster’s topic.

@billjohnson Is any of what has been discussed in this thread helping you at all?

[billjohnson]

Apologies. I’ve been out of the office over the weekend. I will test first thing Monday. Thanks for all the input.

[billjohnson]

I have finally had time to circle back on this. I could not get it to work.
using: <<Start: SELECT(Table[KeyColumn],MOD([NumberColumn],2)=1)>>
I’m OK with introducing table… actually would prefer it…as John Baer suggested but not sure how.
I’m using MS Excel not google sheets
If I could could split all products in the appsheet column into subgroups i.e. Dairy, Poultry, Beef, Seafood, Produce,…
In Sample template: John has [Related Products] and [Related Materials]. How do I split my Products into these type of groups?

[WillowMobileSys]

billjohnson:

I could not get it to work.
using: <<Start: SELECT(Table[KeyColumn],MOD([NumberColumn],2)=1)>>

Just want to be clear what you have tried. The expression above was an example with the intention to replace the Table and Column names with those pertinent to your use case. I’ll presume you knew that. It might help to show what your expression was you tried so we can look for any syntax or logic errors.

billjohnson:

I’m OK with introducing table… actually would prefer it

The table I suggested to help with reporting order, was meant to be a last resort as it would take a bit of coding to pull off. Since that idea, there have been other simpler methods suggested/discovered. In the end, it is what you are most comfortable with.

Creating Child Lists

In my examples, “Related Products” and “Related Materials” are lists of children records to the parent “Orders”. So I have tables “Orders”, “Products” and “Materials”. To create the parent/child relationship, I would add a parent id column in the child tables. In this case, I add the column id “Order ID” in both “Products” and “Materials” tables. In the editor I mark those columns as Ref type and go into the column and “turn on” the “Is part of” property. This links the table in a parent/child relationship AND AppSheet automatically creates Virtual columns for the child lists on the parent record. So, on my “Orders” table I was automatically given the “Related Products” and the “Related Materials” Virtual columns.

You can read more about this relationship in this article (scroll down to " Expressing Ownership Between Tables"):

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so that your data makes sense.

[Bus_Mom]

Woohoo!! This is awesome. I was trying to figure out a way to put multiple columns side by side in a report and adapted the MOD() suggestion (thanks, @Aleksi). I tried using row numbers like the example, but that only worked for 2 columns and those columns were uneven if I hadn’t selected the same number of odd and even rows. Instead, I created virtual columns in the child table (where reports are generated), because it allows for any number of columns and is consistent no matter how many/which Ref items are selected. The formula could be used in the report template “Start:” expression instead so it only runs when a report and doesn’t constantly affect app performance. I haven’t used any sorting, but you could add that to the VC column formula or in the report template depending on your application. In case it’s helpful to anyone else:

NOTE: This only works if the keys in your parent table all have the same length.

Parent Table: inventory; PK [inventoryID] = “inventory”&UNIQUEID()
Child Table: inventoryPrintLabels; Ref column [inventoryIDs] is EnumList with base type Ref so you can select all the inventory items you want to print labels for

1. Create a Virtual Column in the child table for each column (e.g. [_inventoryIDsCol1],[_inventoryIDsCol2], [_inventoryIDsCol3])

2. Set the element Type to Ref of the parent table so you can use the regular report expressions for child records

3. The app formula in each VC column:

SELECT(
parentTableName[parentTableKey],
AND(
IN([parentTableKey],[childTableRefColumn]),
MOD((FIND([parentTableKey],[childTableRefColumn])-1)/(len([parentTableKey])+3),Total#Cols)+1=Col#
)
)

4. Copy the app formula into each VC column, updating the Col# as appropriate (1,2,3…)

5. In the report template create a table with the number of columns you want and fill each column with the usual report expressions. Only the start expression changes in each cell: <Start:_inventoryIDsCol1>,<Start:_inventoryIDsCol2>,etc

APP FORMULA EXPLANATION

SELECT(parentTableName[parentTableKey],
>> Select the Ref IDs from the parent table
AND(
>> Where multiple conditions are true
IN([parentTableKey],[childTableRefColumn]),
>> 1. The Key is one of those referenced in the child table Ref column
MOD((FIND([parentTableKey],[childTableRefColumn])-1)/(len([parentTableKey])+3),Total#Cols)+1=Col#
>> 2A. FIND(): Get the position of the parent row in the list of Refs (based on number of characters).
>> 2B. -1: Subtract 1 to normalise number.
>> 2C. /len() +3: Divide by the the length (# of characters) of the keys +3 (all keys must all be the same length). +3 is the number of characters automatically added in AppSheet in the list (" , "): ID1 , ID2 , ID3 , …
>> 2D. MOD(): Divide the position you just calculated (steps 2A,2B,2C) by the total number of columns you’re trying to make to get the remainder.
>>+1: Add 1 to get the column #.
=Col#
>> 2E. The column # you just calculated equals the column # for this VC.

EXAMPLE
SELECT(
inventory[inventoryID],
AND(
IN([inventoryID],[inventoryIDs]),
MOD((FIND([inventoryID],[inventoryIDs])-1)/(len([inventoryID])+3),3)+1=1
)
)

[jairod]

This is genius!

A couple of questions.

Is the process run against the parent table? If so, do you use the <<start:related[child table] >> expression before the <Start:_inventoryIDsCol1>?

Can you call several fields from the child table? If so, do you call them within the SELECT statement at the VC?

Thank you and best regards