Skip to main content
C
Celtexan
Known Participant
December 21, 2022
Question

Attempting to emulate Excel's VLookup on a fillable PDF form

  • December 21, 2022
  • 1 reply
  • 2136 views

I attempt to mimic Excel's VLookup on a multipage form with input tables on some pages. On the Products page table, there are two columns of fields, pStyle and pRate, where the user inputs data. There are three field columns on the Uniform Order page table: uStyle, uIssue, and uWklyRate. On the Uniform Order page, I have already scripted a function for the uStyle fields to provide a dropdown list of what the user inputs in the pStyle fields (field names prefixed with LstGrp.) I need to pull the pStyle's corresponding pRate to multiply by uIssue into uWklyRate. All of the fields have corresponding row numbers (i.e., pStyle.1, pRate.1, etc.) With my limited experience, I have seen a line of code that makes sense to use to capture the row number in a for loop - var oFld = this.getField(this.getNthFieldName(i)) - but don't see the use for a for loop in this instance. My first step, though, is to locate uStyle's dropdown selection pStyle row number, look on that same row for the pRate, pull the value into the calculation in uRate. Any help would be appreciated. I have attached the form.

This topic has been closed for replies.

1 reply

Thom Parker
Community Expert
Thom ParkerCommunity Expert
Community Expert
December 21, 2022

You've done a good job of field naming, i.e. fields on the same line have the same number appened to the end of the field name. 

So, if a script on a dropdown in the product order table needed to access fields on the same line. All it would need to do is exact the number from it's own field name, to use in aquiring the other fields.

Like this.

 

var cPostNum = event.targetName.split(".").pop();

var oSyleFld  = this.getField("LstGrp.txtSt7yleColor." + cPostNum);

 

This code will work for any kind of field script.

 

***

There are some other issues with your form. I'd suggest removing all field formatting until every other issue on the form is resolved. 

 

 

 

 

Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often
C
CeltexanAuthor
Known Participant
December 22, 2022

Thank you for your assistance, Thom. I am a bit unclear about what you mean by "a script on a dropdown in the product order table needed to access fields on the same line." The dropdown is ddlShirtStyle on the Uniform Order page, which looks for input in the Style & Color column (LstGrp.txtStyleColor) on the Products page to put in the dropdown list. The value selected from the dropdown list on the Uniform Page needs the corresponding Rate (txtProdRate) from the Products page to be sent to the Weekly Rental Rate on the Uniform Order page to be calculated. So, would it need to look like this?:

 

var cPostNum = event.targetName.split(".").pop();

var oStyleFld  = this.getField("txtProdRate." + cPostNum).value;

Thom Parker
Community Expert
Thom ParkerCommunity Expert
Community Expert
December 22, 2022

Using a calculation script on a dropdown to fill the entries in that dropdown is a bad idea. 

Instead, use a Validation script on the  LstGrp.txtStyleColor dropdown.  

 

Once the ddlShirtStyle dropdown field is filled with items, the connection to the original line containing a specific item value is lost.  The code I posted only works for fields in the same line as the field containing the script.

  I'd suggest using this code to get the line number of LstGrp.txtStyleColor field in the same script that is collecting the entries and setting it as the export value of the  ddlShirtStyle item entry, so the selection always has a direct connection back to the original line.  

 

Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices