Hello,
I have a strange bug that I can not seem to clear. For context - App is used for collecting asbuilt data for completion of Telecom construction projects. I have built an excel spreadhseet template that gathers the various data collected and dumps it on the first sheet. From there I have multiple/various additional sheets that grab the data using index() to make a nice sheet ready for Save to PDF which then gets inserted with the original project map PDF. There are Work Location Photo pages displaying the 2 final photos required and a little about them. I use copies of the first Page to pickup the next rows using 1 hidden cell with the index row number to incrementally go down the table on the Data worksheet.
On the first page of any of these sheets the index function of course returns the column name tag “<<[XXXXX]>>” and then page 2 and on are blank in the template as the table area on the Data tab is empty until the output file is created. This works simmingly on all pages and cells except this one cell on the first of the photo sheets. If i leave the index formula in there (displaying “<<[Photo Date]>>”) I get a run-time error in the automation saying it can not find column [Photo Date], this is of course because it is outside of any tables and the column is not in context to the original table that activates the automation. If I delete the INDEX formula in excel on that first page the error will clear but of course not display the date (all sheets after first will display dates no problem).
I have tried an if statement, IF the index value is not a number then make it blank, in attempts to make the cell blank instead of a <<[]>> tag until a date is actually there, this resulted only in a completely empty cell on the output sheet. I have tried to copy and paste the working cells on other sheets to it in case some background data of that cell was affecting it, no change. I have formatted the cell as date which i know shouldnt be read by appsheet anyways, but nothing there either. The 3 cells above all retain their near identicle index formulas and are ignored by appsheet despite being tags themselves. I thought maybe because they are de-referenced tags but there are plenty of other cells throughout the template pages with tags that are not in the main context data and also are not de-references like the Photo Date data.
Any thoughts from anyone much appreciated thank you!
Here is the actual error FYI
The [Photo Date] column is not available to the template in the current context for some reason.
Review the column definition of [Photo Date] in the app table. Make sure there are no expressions that might hide the column when generating the template.
We cannot see your SELECT statement in the template example. If you are using a Slice, ensure your [Photo Date] column is included in the Slice.
Nothing special other than it is a date column, required, and initial value of Today().
This “<<[Photo Date]>>” is actually just an index() pull from the Data Table Tab, not a typed in tag for appsheet to deal with, though it is verified as the one causing the error. Again I had thought maybe appsheet is reading it as a tag to pull data which would explain the error as the template is in context of a different table’s data changes. But there are identicle use cases where the index() pulls the tag and is out of context that do not cause any errors.
This is the formula pulling the filtered data on the Data Table sheet this populates the [Photo Date] Column with no issues.
Hope this helps, let me know what else I can show! Thank you!
To paraphrase, you are using the template to produce a data table on the fly and rely on indexing to retrieve from the data table into the “document”. Interesting approach, I have never seen it done this way. I don’t quite understand how its working.
To Verify some seemingly odd things in the INDEX function…
-
The array starts with row 119. Does the datatable start in row 119?
-
The datatable stops in Column T so the extra few columns seem unnecessary in the INDEX - not a major problem just an observation.
-
The INDEX() function is attempting to pull the row number from cell $AC$36. Is that correct? What is that value? Could it be a blank row?
Yes your paraphrase sounds appropriate. I have gone this way to allow very specifically formatted template pages that are not terribly HTML(to pdf) friendly (as well as tabloid size paper which is our AutoCad Map pdf standard size) and then needing specifics page breaks etc. Instead of each of say the up to 40 photo pages needing differing start/end/filter/select etc formulas for each page to get the next record, I am able to duplicate the sheets with the same exact index formula. The only change is 1 cell counts up on each to specify what row of the array to use. The only Appsheet interaction technically (thought obviously it looks everywhwere) is a dump of filtered records on the data sheet.
1 - The array is 119 as there are a few tables loaded into the data tab for a few different sheets/templates for presenting different data like signal levels or cable lengths etc.
2 - Some of the tables are wider I left the extra space in case other data points needed to be added later and would not have to re-write the index formulas
3 - Yes this is what makes replication for the next record possible, AC36 on the Photo 1 tab has a value of 1, on the Photo 2 tab is has a value of 2 on the third sheet it is 3 ….etc. The value of this cell appears blank so it doesnt not transpose onto the final PDF but is there none the less.
Some examples snips of other sheets/tables, again these just index back to the Data Table tab -
You can see in these they also grab that first row with the <<[]>> tags and from various tables without any issue but for some reason this single [Photo Date] one has issues.
You are triggering all this with an AppSheet Bot…right? Which Table is used as the source for that Bot? Is it the Const Photos table?
I am also not certain how Appsheet deals with an Excel template when there are multiple sheets.
Yes Bot triggers by changes in the Activation Data table. The Data Table tab pulls data from all of these tables though filtered by the project of the row that changed.
Basically this leaves the data ready to go when project is complete just have to convert the excel to PDF and join it to our PDF construction map. Also should we need to do a partial submission its ready at any time (assuming my crews have entered everything in the field =D )
With the multiple sheets it supposedly treats it like normal it just works its way through them 1 by 1 looking for the data tags to fill in with data. Its supposed to work, but also just in case its tricky for it thats also why I preferred this method of all of the data handling happeneing on the first sheet.
Yes, From what I’m reading, AppSheet allows multi-sheet Excel templates. However, it expects “simple” sheets - no tables, formatting, etc. I think the template reader is just getting confused.
It is just that first sheet that’s really needed as the template…right? If you were to hide all but the first sheet, do you get the error then? I’m not certain but I think that would hide those others from the template reader. AND, if I am not mistaken, the INDEX() functions will still operate normally.
This might eliminate the error but I don’t think it will fix the missing date problem.
I think you may have hit the nail on the head for a workaround, and yes i think it is likely just a confused reader. I didnt think to hide whole sheet, when i hid just that cell it didnt seem to matter, I think I will just hide all sheets except the data tab and unhide on the ouput doc when I need to convert to pdf! Thanks very much willow
