Doc template sub list

Hi

Not quite sure how to explain this but going to do my best.

I have created an app that has part of ref rows with ref rows within them.

What I am trying to do is put the 2nd level ref rows in a doc template.

1st level ref rows are fine but I need the ref rows that sit under 1st level to be in the template aswell.

I hope someone gets what i mean.

Thanks

Blake

Hi @BlakeHammond

Please have a look here:

What you are specifically looking for is in the second link, a template is provided in the article :wink:

2 Likes

Hi @Aurelien

Thank you for your reply.

I may be being stupid I can get the parent and grandparent working in standard expression.

but I can’t if I put it between <<Start>> and <<End>>. Am I being stupid?

Thanks
Blake

1 Like

I’m not sure of understand your question ?
Never say “stupid” anyway :slight_smile:

Your syntax must be, basically :

<<Start:[Related children]>> 
  <<[ColumnA]>> <<[ColumnB]>>
  <<Start:[Related grandChildren]>> 
    <<[ColumnXgrandChildren]>> <<[ColumnYgrandChildren]>>
   <<End>>
 <<End>>

(I may be wrong but I’m pretty sure that’s it)

1 Like

Hey :slight_smile:

I tried that as per picture below. :slight_smile:

But I get the following error when running.

Error: ‘New process’ task ‘Send LogBook’ Body template. Expression ‘[Areas schedules]’ is invalid due to: Unable to find column ‘Areas schedules’, did you mean ‘Zone Schedules’?. Error: ‘New process’ task ‘Send LogBook’ Body template. Start expression ‘[Areas schedules]’ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the ‘Key’ column of the referenced table. Error: The webhook body is empty

The hierachy goes

  • Intruder commissioning Form
    Under Above) Ref Row Panel Tests
    Under Above) Ref Row Areas Schedule
    Under Above) Red Row Area Timers

Thanks

Blake

1 Like

OK, thanks for your inputs.

That kind of result you are trying to get is very tricky to build, especially because of the inconsistency of column widths.
You will need to use some workaround :crazy_face:

To my knowledge, you can only deal with one start statement in a table.
But, you can add a table inside another cell, and use a nested Start statement inside this one.
I don’t know the limit of this nesting.

I suggest you have a look to these posts:

Let us know if you succeed into your project :slight_smile:

EDIT
I just noticed the error message you got with area schedule.
The correct sentence should be:
<<Start:[ Related Areas Schedules]>> ...<<End>>
because the second Start statement is evaluated as nested in the first statement, so it’s like you are located in the Panel Tests Table (unless you renamed the default column name with Area Schedule…in this case, please ignore this remark)

2 Likes

Hi @Aurelien

Sorry taken a while to reply got caught up with other things. Back on it now :slight_smile: .

I managed to get the array to work by adding endpoints in each column.

like so.

However, I am now struggling again with AppSheet this time. I have a JSON payload template I have attached.

{
   "Certificate ID": "<<[Certificate ID]>>",
   "Comissioned date": "<<[Comissioned date]>>",
   "Customer Name": "<<[Customer Name]>>",
   "Customer Address": "<<[Customer Address]>>",
   "Site Name": "<<[Site Name]>>",
   "Site Address": "<<[Site Address]>>",
   "Customer ID": "<<[Customer ID]>>",
   "Panel Upload File": "<<[Panel Upload File]>>",
   "Notes": "<<[Notes]>>",
   "Send Docket": "<<[Send Docket]>>",
   "Texecom Equipment Schedule": [
      <<Start: [Equipment Schedules]>>
      {
         "Equipment ID": "<<[Equipment ID]>>",
         "Certificate ID": "<<[Certificate ID]>>",
         "Part No.": "<<[Part No.]>>",
         "Description": "<<[Description]>>",
         "QTY": "<<[QTY]>>"
      },
      <<End>>
   ]
   "Texecom Zone Schedule": [
      <<Start: [Zone Schedules]>>
      {
         "Zone ID": "<<[Zone ID]>>",
         "Certificate ID": "<<[Certificate ID]>>",
         "Zone Number": "<<[Zone Number]>>",
         "Wiring Location": "<<[Wiring Location]>>",
         "Zone Device": "<<[Zone Device]>>",
         "Type": "<<[Type]>>",
         "Area": "<<[Area]>>",
         "Zone wiring": "<<[Zone wiring]>>",
         "Zone Text": "<<[Zone Text]>>",
         "Chime Options": "<<[Chime Options]>>",
         "Attributes 1": "<<[Attributes 1]>>",
         "Attributes 2": "<<[Attributes 2]>>",
         "Custom Zones": "<<[Custom Zones]>>",
         "Custom Attributes": "<<[Custom Attributes]>>",
         "Notes": "<<[Notes]>>"
      },
      <<End>>
   ]
   "Texecom Keypad schedule": [
      <<Start: [Keypad schedules]>>
      {
         "Keypad ID": "<<[Keypad ID]>>",
         "Certificate ID": "<<[Certificate ID]>>",
         "Network": "<<[Network]>>",
         "Number": "<<[Number]>>",
         "Areas": "<<[Areas]>>",
         "Zone 1 Mapped": "<<[Zone 1 Mapped]>>",
         "Zone 2 Mapped": "<<[Zone 2 Mapped]>>",
         "Volume": "<<[Volume]>>",
         "Keypad Sounds": "<<[Keypad Sounds]>>",
         "Keypad Options": "<<[Keypad Options]>>",
         "Notes": "<<[Notes]>>"
      },
      <<End>>
   ]
   "Intruder Panel Tests": [
      <<Start: [Panel Tests]>>
      {
         "Intruder ID": "<<[Intruder ID]>>",
         "Certificate ID": "<<[Certificate ID]>>",
         "Panel Type": "<<[Panel Type]>>",
         "Panel Location": "<<[Panel Location]>>",
         "Serial Number": "<<[Serial Number]>>",
         "Battery Charging Voltage": "<<[Battery Charging Voltage]>>",
         "Power Supply Current (Normal)": "<<[Power Supply Current (Normal)]>>",
         "Power Supply Current (In Alarm)": "<<[Power Supply Current (In Alarm)]>>",
         "Battery Current (Mains Supply Disconnected)": "<<[Battery Current (Mains Supply Disconnected)]>>",
         "Input Voltage To SAB": "<<[Input Voltage To SAB]>>",
         "SAB Charging Current Checked": "<<[SAB Charging Current Checked]>>",
         "Texecom Areas Schedule": [
            <<Start: [Areas Schedules]>>
            {
               "Areas ID": "<<[Areas ID]>>",
               "Certificate ID": "<<[Certificate ID]>>",
               "Area": "<<[Area]>>",
               "Arming Mode": "<<[Arming Mode]>>",
               "Area Text": "<<[Area Text]>>",
               "Arm Area With": "<<[Arm Area With]>>",
               "Disarm Area With": "<<[Disarm Area With]>>",
               "Notes": "<<[Notes]>>",
               "Texecom Area Timers": [
                  <<Start: [Area Timers]>>
                  {
                     "Area ID": "<<[Area ID]>>",
                     "Areas ID": "<<[Areas ID]>>",
                     "Area Timers": "<<[Area Timers]>>",
                     "Value": "<<[Value]>>",
                     "Units": "<<[Units]>>"
                  },
                  <<End>>
               ]
            },
            <<End>>
         ]
      },
      <<End>>
   ]
   "Texecom Expander Schedule": [
      <<Start: [Expander Schedules]>>
      {
         "Equipment ID": "<<[Equipment ID]>>",
         "Certificate ID": "<<[Certificate ID]>>",
         "Network": "<<[Network]>>",
         "Number": "<<[Number]>>",
         "Areas": "<<[Areas]>>",
         "Aux. Input": "<<[Aux. Input]>>",
         "Volume": "<<[Volume]>>",
         "Expander Sounds": "<<[Expander Sounds]>>",
         "Location": "<<[Location]>>"
      },
      <<End>>
   ]
   "Intruder Handover Checklist": [
      <<Start: [Handover Checklist]>>
      {
         "Handover ID": "<<[Handover ID]>>",
         "Certificate ID": "<<[Certificate ID]>>",
         "Check that the installation is in strict accordance with the Specification, complies with BS 4737 and is to a high standard of workmanship.": "<<[Check that the installation is in strict accordance with the Specification, complies with BS 4737 and is to a high standard of workmanship.]>>",
         "Check that the customers premises are left in a tidy condition.": "<<[Check that the customers premises are left in a tidy condition.]>>",
         "Check all detection circuits are clearly indicated.": "<<[Check all detection circuits are clearly indicated.]>>",
         "Log all detection circuit insulation and continuity/resistance measurements": "<<[Log all detection circuit insulation and continuity/resistance measurements]>>",
         "Check mains connection is permanently connected and NOT by plug and socket.": "<<[Check mains connection is permanently connected and NOT by plug and socket.]>>",
         "Check that the supply fuses to the alarm installation have the correct rating.": "<<[Check that the supply fuses to the alarm installation have the correct rating.]>>",
         "Check that all batteries are clearly marked with date of installation.": "<<[Check that all batteries are clearly marked with date of installation.]>>",
         "Log the normal DC current loadings of all power supplies.": "<<[Log the normal DC current loadings of all power supplies.]>>",
         "Remove the mains supply and check that the battery voltage of all equipment is within the specified limits and the system operates normally": "<<[Remove the mains supply and check that the battery voltage of all equipment is within the specified limits and the system operates normally]>>",
         "Check that there is adequate standby battery capacity to meet BS 4737.": "<<[Check that there is adequate standby battery capacity to meet BS 4737.]>>",
         "Check the operation of the audible alarm on system activation and when the hold off voltage is removed from any self activating device.": "<<[Check the operation of the audible alarm on system activation and when the hold off voltage is removed from any self activating device.]>>",
         "Check remote signalling apparatus ensuring correct transmission and receipt of all conditions.": "<<[Check remote signalling apparatus ensuring correct transmission and receipt of all conditions.]>>",
         "Check that the engineer only reset facilities are available where applicable.": "<<[Check that the engineer only reset facilities are available where applicable.]>>",
         "Check the operation of all tamper detection circuits.": "<<[Check the operation of all tamper detection circuits.]>>",
         "Check every detector for correct operation through to the Control Units.": "<<[Check every detector for correct operation through to the Control Units.]>>",
         "Check and record the area or volume of coverage of movement / vibration detectors.": "<<[Check and record the area or volume of coverage of movement / vibration detectors.]>>",
         "Check beam interruption detectors for correct alignment.": "<<[Check beam interruption detectors for correct alignment.]>>",
         "Check that system operating procedure is displayed near to Control Unit.": "<<[Check that system operating procedure is displayed near to Control Unit.]>>",
         "Check the exit/entry route time delay (if used) for correct setting and record times.": "<<[Check the exit/entry route time delay (if used) for correct setting and record times.]>>",
         "Set system. Operate a detector device. Check the resulting alarm condition is correctly signalled.": "<<[Set system. Operate a detector device. Check the resulting alarm condition is correctly signalled.]>>",
         "The Customer is to be shown the extent of the protection and correct operation of the system.": "<<[The Customer is to be shown the extent of the protection and correct operation of the system.]>>",
         "Check that all documentation in accordance with BS 4737 is correct. (It is recognised that some checks under this heading may be subsequent).": "<<[Check that all documentation in accordance with BS 4737 is correct. (It is recognised that some checks under this heading may be subsequent).]>>",
         "Record the number of the Certificate issued in accordance with the Council's Rules.": "<<[Record the number of the Certificate issued in accordance with the Council's Rules.]>>",
         "Obtain customers signature acknowledging receipt of system keys.": "<<[Obtain customers signature acknowledging receipt of system keys.]>>",
         "Check that all surplus materials and equipment are cleared from the site.": "<<[Check that all surplus materials and equipment are cleared from the site.]>>"
      },
      <<End>>
   ]
   "Intruder Customer Sign Off": [
      <<Start: [Customer Sign Off]>>
      {
         "Signoff ID": "<<[Signoff ID]>>",
         "Certificate ID": "<<[Certificate ID]>>",
         "Customer Name": "<<[Customer Name]>>",
         "Customer Address": "<<[Customer Address]>>",
         "Site Name": "<<[Site Name]>>",
         "Site Address": "<<[Site Address]>>",
         "Customer ID": "<<[Customer ID]>>",
         "Customers Signature": "<<[Customers Signature]>>",
         "Engineer Signature": "<<[Engineer Signature]>>",
         "Signature Date": "<<[Signature Date]>>"
      },
      <<End>>
   ]
}

The error I am getting is

Error Message
Error encountered in step with name [Log Book]: Error: Failed to parse JSON due to After parsing a value an unexpected character was encountered: ". Path '['Texecom Equipment Schedule']', line 1, position 1237.. Invalid JSON value starts with: "Texecom Zone Schedule": [{"Zone ID": "bb0346b5","Certificate ID": "5063a956","Zone Number": "1","Wi
Execution Time (In seconds)
13

I am trying to send webhook to zapier for zapier to do google doc formatting as I have some pages that are landscape and some that are portrait and Appsheets PDF creator doesn’t respect that.

Any ideas on a workaround to the error

Thanks

Blake

Hi @Suvrutt_Gurjar & @Steve

Any ideas on this one?

Thanks

Blake

image

image

Missing comma?

1 Like

Hi Steve

Where would the comma go? The JSON file was auto-created by AppSheet so assumed that was correct.

Thanks

Blake

1 Like

HI Steve

I think I found the issue.

You where correct a comma was missing between ] & “Texecom” on all lines.

But then I got the issue.

Error encountered in step with name [Log Book]: Error: Failed to parse JSON due to Unexpected character encountered while parsing value: <. Path ‘’, line 0, position 0…

Once I took it out of the auto-created template and entered it straight into the body text it works.

Is this a bug with the auto-created Json Templates ?

Thanks

Blake

3 Likes