I have an Excel sheet with lookup tables that I need to convert to a fillable PDF. I am currently using AEM 6.4 to develop the form. I cannot use an external database, the form has to be portable for use by field personnel. I am not new to scripting, but I am not a programmer, this is out of my wheelhouse. Any help is greatly appreciated.
My form consists of the following:
A user facing page that allows the user to enter values in an expandable table called adj_table. The column I need to use as the lookup value cell is called date, the value I need to lookup is called m_index and is placed in adj_table in a cell called index. We then use the index value to calculate and index adjustment. There are other variables, but solving the index issue will give me a good start and something to work from as an example.
In another table that is hidden, we enter the monthly index in an expandable table. The table is named m_table and has the cells m_date and m_index. This table can be updated by the field personnel by clicking a button to show the table.
I need to compare the the adj_table date cell value with the values in m_table, m_date so that it retrieves m_index from the correct month. the Excel formula we use includes error trapping and looks like this: =IF(ISERROR(VLOOKUP($A3,monthly,2)),"Index Not Found",VLOOKUP($A3,monthly,2))