Making template report with select start, end

[MJvanEwijk]

Hi can someone help: I am trying to make small expression to fill data from related table into a word template.

The template word doc is filled with data form the current form. After saving the template needs to add client info like address, phone, mail etc… The work order form has a relation with the client info. I try to write expression within the cells with select… start…end… it doesn’t seems to work. see picture

[Derek3]

If the Work Order only has one Client, then you may want to use a LOOKUP() function to pull the related information. https://help.appsheet.com/en/articles/2357309-lookup.

Start expressions are used to make multiple tables or a table with multiple rows. For example, if the Work Order has several Clients related, then you could use a start expression to make a table for each client.

Here’s a sample app you can check out for an example of Start expressions. The “PDF Report” workflow shows a few examples of how to set them up.

[MJvanEwijk]

Hi Derek, thank you, where can I find the example app?

[Derek3]

Ah yes, thinking about attaching a link does not actually attach the link.

Here you go!
https://www.appsheet.com/samples/Demo-of-template-for-PDF-Generation?appGuidString=093161f4-2604-45d...

[MJvanEwijk]

I looked at the demo app but can not see how template is build…no access?

[MJvanEwijk]

Thanks Derek, what is better, making virtual columns in the sheet with lookup or the expression in the word template??

[Derek3]

For most calculations, I recommend using a virtual column if you will reuse the result other places in the app. Put the calculation in the template if it will only be used in the workflow.

To see the template in the example app, first copy the app to your account. This will create a copy of the template in the cloud storage system you used when you signed into AppSheet.

[MJvanEwijk]

Hi Derek, thanks, it works super. Only one thing: sometime the signature![signature not show is not presented in the word template form, what can be the reason. It is random, so not consequent. see picture…

[Derek3]

I would start by checking the following:

• Open the template and confirm you have the field typed correctly: <<[SignatureColumnName]>> where SignatureColumnName is replaced with the name of your column
• After making a change to your app, click the down arrow next to the Save button. In the drop down list, click “Save and Verify”. By clicking Save and Verify, AppSheet will check your templates for syntax errors.
• Find the row of your data source that corresponds to a report with a blank “Signature Technician” field. Check if the “Signature Technician” column for that row contains a relative file path to the signature image file.
• Check the folder in your cloud storage location (e.g. Google Drive) where the file lives. Make sure there is file with the same location and name as is specified in the data source.

Random behavior can be tricky to pin point, but those steps would rule out most of the likely possibilities.

[MJvanEwijk]

Hello,
I am like to select a list of project_nr’s from another table with where the date is latest. So do not show duplicates but only the last date. I try to do that in valid_if and enum but it not seems to work.

[Lynn]

Hi @MJvanEwijk
What was the expression you tried. What was wrong. Did you get any error messages?

[MJvanEwijk]

Hi Lynn, thanks for your reply.
Now if have Ref to the source table but I would like to show only the list of values which I did not selected before, so something like: select(WorkOrder Man Hours[Project_ID], [Project_ID] from the table is not in the [Project_ID] column form the table/form I am selecting from).
So I select Project_ID’s from the WorkOrder Man Hours table but I only want to show to Project_ID’s in the pulldown list which I didnot select before in the approval table/form. I hope you understand, it is difficult to explain. Basically I do not want to select the value again if I already have selected it before. Regards Muk

[MJvanEwijk]

Thank you Derek, I found the problem. Other question: who can help?
I need to connect the app to a ERP application (AFAS software, https://help.afas.nl/help/EN/SE/App_Conect.htm?query=connector) Zapier is not possible. What is the best way to set this up. We need to get several tables/columns from the ERP and update a table/columns to the ERP. Is that possible and where can I find help. Thanks Muk

[LeventK]

@MJvanEwijk
Your AFAS software/platform have a REST API service:
https://help.afas.nl/help/EN/SE/App_Cnr_Rest_Api.htm?query=REST%20API
You can create a Google Apps Script, publish it as a web-app, use its endpoint URL in a webhook workflow rule and trigger their API to POST data.

[MJvanEwijk]

Hi Levent, thank you for your reply.
Google Apps Script, publish it as a web-app, Can I find any info on this?
publish it as a web-app, need to copy the script into the appsheet workflow?
So I have not much experience with this so we need to teach our self’s.

I use office 365 Excel as backup for data.

[LeventK]

If you are using Excel, than Google Apps Script can’t be a choice for you unfortunately. VBA shall be used instead. I know that Excel can monitor cell value change to trigger a VBA function but not sure if it can catch a cell change that’s triggered by AppSheet. Being an Excel user, your way is definitely thru VBA here.

[Derek3]

Hi Muk,

In terms of built-in functionality, there are a couple tools in AppSheet that can help.

• Webhooks allow you to send information to a REST API endpoint. However, you are not able to do anything with the response from the API. So these calls are “fire and forget.”
• AppSheet has a REST API that allows external APIs to send information to your app.

AppSheet doesn’t directly integrate with AFAS, but the platform does integrate with Zapier. Since you mentioned Zapier is not an option, you could potentially write your own script as Levent mentioned. There are some great examples on the community, but extensions with AppScript or VBA are not covered in the core AppSheet documentation.

[MJvanEwijk]

Hello all, I used power automate to get data from AFAS ERP into my onedrive excell. It works super. From appaheet direct to AFAS update connector. Thanks for all the help.

[MJvanEwijk]

Hi, I need some help. I have a table name WorkOrder Man Hours. I would like to make summary a report. Just a simple report sorted by Project _Id with all Names ([Volledige_Naam]) and hours in a table. More names with samen Porject_ID is possible… how can I do that… see

Thanks Muk

[Steve]

Please contact support@appsheet.com for help with this.

[MJvanEwijk]

Thanks problem solved!