Re: PDF Template triple nesting error…maybe

tomcollins945

I'm trying to add a "Catagories" heading to my equipment list in my quotes.

There are lots of other articles with similar questions but I can't get any of those solutions to work, and I think it's because I need 3 Start expressions, or 2 start expressions and 1 If expression, and that seems to exceed some limit.

I keep getting a silly error, "2 mismatched Starts and 2 mismatched Ends". 4 odd socks is just 2 pairs of socks!

There are Gigs, each gig has rooms, each room then has equipment. Each piece of equipment has a category, I want to clump the categories together, and put a heading at the top of each one:

Audio:

Speakers

Sounddesk

Lighting:

Stage light (each line also has QTY, Price, Days, Amount, Quote_notes, internal_notes...but I've removed all of that until i can get this to work)

Here's a piece of code that returns a list of the equipment:

<<Start: [GigID].[Related GigRooms]>>

<<[Room]>>

<<Start: OrderBy([Related GigEquipments], [Sort Order], FALSE)>>

<<[Name]>>

<<end>>

Sort Order is a legacy which I'll get rid of once I can get this grouping/heading thing to work

Here’s my latest attempt:

<<Start:[GigID].[Related GigRooms]>>

<<[Room]>>

<<Start: OrderBy([Related GigEquipments], [Category], FALSE, [Sort Order], FALSE)>>

<<If([_THISROW].[Category] <> [_PREVROW].[Category])>>

<<[Category]>>

<<Endif>>

<<[Name]>>

<<End>>

This throws this error:Error encountered in step with name [Step: Create Quote A]: Error: Task 'Step: Create Quote A' Body template. Found 2 unmatched 'Start', 2 unmatched 'End', 1 unmatched 'EndIf'. They are: <<Start:[GigID].[Related GigRooms]>>', '<<Start: OrderBy([Related GigEquipments], [Category], FALSE, [Sort Order], FALSE)>> ', '<<Endif>>', '<<End>>', '<<End>> Error: The document body is empty

Gemini and I have been going around in circles for some time, would love some help!

Suvrutt_Gurjar

@tomcollins945 wrote:

[_PREVROW].[Category])>>

Could you mention what you mean by this syntax.

Appsheet does not have a default syntax of [_PREVROW]

It has default syntax of [_THISROW], [_THISROW_BEFORE] and [_THISROW_AFTER]

Is [_PREVROW] , a reference column name that you have created?

Of course, there could be additional errors but we could start with the above.

tomcollins945

Thanks for that, I tried this:

<<Start:[GigID].[Related GigRooms]>>

<<[Room]>>

<<Start: OrderBy([Related GigEquipments], [Category], FALSE, [Sort Order], FALSE)>>

<<If([_THISROW_BEFORE].[Category] <> [_THISROW_AFTER].[Category])>>

<<[Category]>>

<<Endif>>

<<[Name]>>

<<End>>

Same silly Error:Error encountered in step with name [Step: Create Quote A]: Error: Task 'Step: Create Quote A' Body template. Found 2 unmatched 'Start', 2 unmatched 'End', 1 unmatched 'EndIf'. They are: <<Start:[GigID].[Related GigRooms]>>', '<<Start: OrderBy([Related GigEquipments], [Category], FALSE, [Sort Order], FALSE)>> ', '<<Endif>>', '<<End>>', '<<End>> Error: The document body is empty

TeeSee1

A casual glance made me notice that <<IF expression>> should be <<IF: expression>> (needs a colon), resulting in an unmatched <<EndIf>>

https://support.google.com/appsheet/answer/11568425?hl=en

tomcollins945

Thanks TeeSee1! That did fix that error, however, the If function is not working properly, because it doesn't print the [Category], so the IF is never true.
Slowly getting there!

TeeSee1

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Single-category-headers-in-workflow-templates-fo...

tomcollins945

I've tried both solutions proposed in that link (which I already tried many times before posting here) and can't get it to work.)Steves versionError with Steves versionMarcs versionError with marcs version

Gemini has run out of patients, which is kinda funny, it kept giving me functions that didn't exsist like Row(), Number(), PREVROW.

Hopfully I can find more meaninful help here, please and thank you

TeeSee1

I was not sure of you exact architecture. So based on the following one, I made a sample report that uses the technique described in the post I pointed to earlier. I used my technique (posted towards the end of the thread) assuming you have a separate CATEGORIES table.

Tables (Each gig can have multiple rooms and each room can have its own set of equipment)

GIGS: id.gig, name.gig, etc

ROOMS: id.room, etc

EQUIP: id.equip, name.equip, ref.category, etc

CATEGORIES: id.category, name.category

GIG_ROOMS: id.gig_room, ref.gig, ref.room, etc

GIG_EQUIP: id.gig_equip, ref.gig_room, ref.equip, etc

Template:

GIGS
id.gig: <<[id.gig]>>
name.gig: <<[name.gig]>>

<<Start: [Related GIG_ROOMSs]>>
GIG_ROOM
id.gig_room
<<[id.gig_room]>>
ref.gig
<<[ref.gig]>>
ref.room
<<[ref.room]>>


GIG_EQUIP

<<START: ORDERBY(SELECT(CATEGORIES[id.category],IN([id.category],SELECT(EQUIP[ref.category],IN([id.equip],SELECT(GIG_EQUIP[ref.equip],[ref.gig_room]=[_THISROW-3].[id.gig_room])))),TRUE),[name.category],TRUE)>>
CATEGORY: <<[id.category].[name.category]>>
id.gig_equip
Equipment
ref.equip
<<Start:SELECT(GIG_EQUIP[id.gig_equip],AND([ref.gig_room]=[_THISROW-2].[id.gig_room],[ref.equip].[ref.category]=[_THISROW-1].[id.category]))>><<[id.gig_equip]>>
<<[ref.equip].[name.equip]>>
<<[ref.equip].[ref.category].[name.category]>><<End>>


<<End>>
*************************************************************************************************************
<<End>>

Result:

tomcollins945

Wow, thanks TeeSee, Appsheet was able to render this one, however it didn't quite work. It appears to have printed the entire table for GigEquipment, and not just the records relating to that room. Thanks so much for spooling up a new data base just to help me out.
Here's the code i used:

<<Start:[GigID].[Related GigRooms]>>

<<[Room]>>

<<START: ORDERBY(SELECT(EquipmentCategories[EquipmentCategoryID],IN([EquipmentCategoryID],SELECT(Equipment[Category],IN([EquipmentID],SELECT(GigEquipment[EquipmentID],[GigRoomsID]=[_THISROW-3].[GigRoomsID])))),TRUE),[Category],TRUE)>>

<<[Category]>>

<<Start:SELECT(GigEquipment[GigEquipmentID],AND([GigRoomsID]=[_THISROW-2].[GigRoomsID],[EquipmentID].[Category]=[_THISROW-1].[EquipmentCategoryID]))>>

<<[EquipmentID].[Name]>>

<<End>>

TeeSee1

Your template looks good to me.

What happens if you add the expression in blue. Would it still display all the rows in GigEquipment?

<<Start:[GigID].[Related GigRooms]>>

<<[Room]>>

<<Start:SELECT(GigEquipment[GigEquipmentID],[GigRoomsID]=[_THISROW-1].[GigRoomsID])>>

<<[EquipmentID].[Name]>>

<<End>>

<<START: ORDERBY(SELECT(EquipmentCategories[EquipmentCategoryID],IN([EquipmentCategoryID],SELECT(Equipment[Category],IN([EquipmentID],SELECT(GigEquipment[EquipmentID],[GigRoomsID]=[_THISROW-3].[GigRoomsID])))),TRUE),[Category],TRUE)>>

<<[Category]>>

<<Start:SELECT(GigEquipment[GigEquipmentID],AND([GigRoomsID]=[_THISROW-2].[GigRoomsID],[EquipmentID].[Category]=[_THISROW-1].[EquipmentCategoryID]))>>

<<[EquipmentID].[Name]>>

<<End>>

tomcollins945

Yes, it printed every line of that table....in blue!
I have left out a critical bit of information, this report is generated from a table called GigQuotes which has a GigQuoteID and a GigID. This is likely why the app keeps having troulbe finding the GigRoomID.

Everything has worked in the past because it uses the GigID to find "related gigrooms" and then inside that it finds "related GigEquipment"

Sorry for leaving out the important context!

TeeSee1

I still do not see why the expression displays all the eq in the GigEquipment table without knowing the exact relations between your tables.

tomcollins945

ok, what's the best way to let you know how that info? screen shots of all the tables?

TeeSee1

Just table keys and ref relations

tomcollins945

Gigs

GigID

Gig

Equipment

EquipmentID

Name

Category Ref

EquipmentCategories

EquipmentCategoryID

Category Name

GigRooms

GigRoomsID

GigID Ref

Room

GigEquipment

GigEquipmentID

GIGID Ref

EquipmentCategoryID Ref

EquipmentID Ref

GigRoomsID Ref

Name (Initial Value=[EquipmentID].[Name])

GigQuotes (From which the template is created)

GigQuoteID

GigID

Version Number

Main difference being the lack of a Room Table, otherwise looks the same just with a less professional naming convention (I'll add that to my todo list!)

TeeSee1

I modified my demo app to match your architecture and this is a renewed template

Task for Create Eq List
QUOTES
id.quote: <<[id.quote]>>
ref.gig: <<[ref.gig]>>

<<Start: [ref.gig].[Related GIG_ROOMSs]>>
ROOM: <<[id.gig_room].[ref.room]>>

<<START: SELECT(GIG_EQUIP[ref.category], [ref.gig_room]=[_THISROW-1].[id.gig_room],TRUE)>>

 ** EQ Category: <<[id.category].[name.category]>> **

<<START: SELECT(GIG_EQUIP[id.gig_equip],AND([ref.gig_room]=[_THISROW-2].[id.gig_room],[ref.category]=[_THISROW-1].[id.category]))>>
    EQ: <<[ref.equip].[name.equip]>>

<<End>>
<<End>>
<<End>>

This is a result output.

tomcollins945

This verison printed "Category" 4 times, with no other data. This is a good step i suppose because there are 4 relevant categories on the one I'm testing. Here's the template:

<<Start:[GigID].[Related GigRooms]>>

<<[Room]>>

<<Start:SELECT(GigEquipment[Ref.Equip_Category], [Ref.Gig_Room]=[_THISROW-1].[GigRoomsID],True)>>

Category<<[EquipmentCategoryID].[Category Name]>>

<<Start: Select(GigEquipment[GigEquipmentID],AND([Ref.Gig_Room]=[_THISROW-2].[GigRoomsID],[Ref.Equip_Category]=[_THISROW-1].[EquipmentCategoryID]))>>

Equipment: <<[EquipmentID].[Name]>>

<<End>>

The only thing I can see between our two apps that are different, is I don't have rooms as a reference.
Thanks for your help on this, I'm still trying to fight with Gemini, but he doesn't know any correct sytax, which is good because I'm learning from his mistakes!