Can I import the data from a column in a tab denim file and use it to populate the items in a dropdown/combo box, loading on document open?

[nathang78560588]

Hello experienced JSers.

I'm starting to get to grips with a bit of Javascript knowledge, from being a total fresher a couple of weeks ago. Now that my eyes have been opened to to what is possible, I want to be able to utilise what I've learnt as much as I can.

I have a fillable form, created with Acrobat Pro DC and it contains a lot of fields for customer info (first name, last name, three address fields, telephone number, and email address, etc). One of them is simply called "Company", and is for the company name that the customer works for.

I have set up a dropdown list/combo box for Company, and I've got it linked to a tab delimited .txt file, using a folder-level script as follows:

var Trusted_importAddress = app.trustedFunction(importAddress);
function importAddress()
{
   app.beginPriv();
   var cCoNoforImport = this.getField("Company").value;
    this.importTextData("/**FULLFILEPATH**/**FILENAME**.txt", cCoNoforImport);
   app.endPriv();
}

The script is being called by the following code, onBlur from the actions section of the dropdown box's properties.

Trusted_importAddress(this);

At the moment, it is all working well, and on the selection from the user of the item in the Company dropdown, the form fills in all the relevant fields on the rest of the form, using the data from the .txt file.

Great.

The thing is, I have had to manually specify all the company names and add the export values as the row numbers in the text file, putting them all into the options section of the box's properties one by one.

What I'm looking for is this:

When the form file loads in acrobat, I want it to import the data from the Company column in the .txt file, and automatically populate the Company Dropdown box with all of the items in that column. (Then, selecting the company name in the drop down will still populate th eother fields with the relevant data, as happens now.)

There are a couple of other requirements, but I'll be happy with the above for now, and the rest can come later, but if anyone can help with combining the above with the below, I'll be over the moon:

In an ideal world, I would like the ability to be able to type in entries for a customer whose details are not in my .txt file. That's not an issue, but It would be amazing if once the details had been filled in on the form, the form could have a button (or a custom menu item, based on security requirements) that could get the data that I have entered into the form, and put it into the .txt file, as a new line, saving the txt file with the new customer for future use.

Whether that would require  getting the entire txt file as a massive array, and adding the new data to the end, then saving it as a new file but with the same name, so that it overwrites the old one, or whether the new info can be appended to the end of the existing file, and saved that way, I'm not sure.

If anyone reading this is able to help with any part of this rather large reques, I will be eternally grateful. Ireally appreciate the time and effort of anyone who trues to help on forums like this, whether or not I am abe to mak theur suggestions work in practice.

Thanks in advance for your time.

Nathan

[try67]

Yes, it is possible, but it requires a different, more complex, script.

You would basically need to read the contents of the text file (using util.readFileIntoStream, for example), parse them and convert to an array and then apply them to the field using setItems.

Notice that when you do that it overwrites the field's current value so you'll probably want to add code to save that value before setting the new items, and then re-apply it afterwards.

[Thom Parker]

Read these two articles:

https://acrobatusers.com/tutorials/getting-external-data-into-acrobat-x-javascript

https://www.pdfscripting.com/public/ExcelAndAcrobat.cfm

The onBlur event is not the correct place to be modifying field values.  It would be much better to use the validation event, to the import only happens on a value change.

[logistics227043683]

It is certainly possible, and Try has it down. I would pull the text file into the script via readFileIntoStream, like this:

var oFile = util.readFileIntoStream(PLACE cPATH HERE.txt);
var cFile = util.stringFromStream(oFile, "utf-8");
var yourCode = cFile.split("\n");

After you have the cPath set up for your text file, I would create a loop and push the first index into an array.

var i;
var loopGet = [];
for (i = 0; i < yourCode.length; i++){
     loopGet.push(yourCode[0]);
}

This should theoretically push the entire first column of your tab delimited text file into the variable, "loopGet."

tweak the code as needed.

[nathang78560588]

That all looks very much like what I’m looking for. I hope to get the chance to ay with that this evening and see if I can get it working. Thanks so much, I need that sort of help, as I’m completely new to this, with no coding experience e whatsoever, and although I’m learning fast, I’m aware that I don’t know any of the basics that are so vital to understanding the sort of discussion of solutions that people are so kind to give me: unfortunately I do t have enough knowledge yet to know what I should do unless I’m given the example codes as you have done here, so I’m incredibly grateful to you!

One question before I dive in: is this code that should be places in a config file at folder level, or is it a validation script?

Thanks so much.

NathanG

[logistics227043683]

I personally suggest writing the script as a Document-level script, that way it runs when you open your document, but that is just me.

[nathang78560588]

Well, I tried, and I failed!

I'm afraid I simply don't know enough about this, and I have no idea how to implement the suggestions you have so kindly put forward.

If that kindness can extend a little further, I'd really appreciate some spoon feeding with the steps I need to take, and the code I need to put in place, and a general, all-around holding of the hand!

I've been tearing my hair out over this for many hours now, and I'm no closer to where I want to be than when I started.

Any help in idiot-proof language will be gratefully received.

Thanks in advance for your time.

Best wishes,

NathanG

[Thom Parker]

Which part are you having trouble with?

Where you able to open the external file and read the data into a string?

[nathang78560588]

Hello Thom,

I've probably got myself into a terrible muddle, as I've been going round in circles trying this and that, and basically groping in the dark, so it's quite likely that I've put in errors that just shouldn't have happened if I'd left well alone!

Here is the way things stand at the moment:

In my folder-level script,  I have the following two trusted functions:

var Trusted_readContactDetails = app.trustedFunction(readContactDetails);
function readContactDetails()
{
app.beginPriv();
var oFile = util.readFileIntoStream("/Macintosh HD/Users/na***an/Documents/M***EY/So***ts.txt");  
var cFile = util.stringFromStream(oFile, "utf-8");  
var yourCode = cFile.split("\n");  
var i;  
var loopGet = [];  
for (i = 0; i < yourCode.length; i++){  
     loopGet.push(yourCode[1]);  
}  
this.getField("Company").setItems(loopGet, 0);
app.endPriv();
}
var Trusted_importAddress = app.trustedFunction(importAddress);
function importAddress()
{
app.beginPriv();
var cCoNoforImport = this.getField("Company").value;
importTextData("/Macintosh HD/Users/na***an/Documents/M***EY/So***ts.txt", cCoNoforImport);
app.endPriv();
}

(I've redacted the file paths for privacy, but I know them to be correct...)

I've attached the call for Trusted_readContactDetails in the actions for the page properties of the pdf (accessed from the thumbnail pane on a right-click) with:

On Page Open > Run a Javascript >

Trusted_readContactDetails();

The fieldname for the dropdown that I want to populate with the info from the txt file is "Company".

I have a global variable set up at document level, called "vCompany".

the txt file has 7 lines in total (Eventually there will be several hundred). The first line is my field names that need to be filled on selection from the dropdown box.

They are:

Company

FirstName

LastName

StreetAddress

Town

Postcode

TelephoneNumber

Email

The 2nd line is the info that I want to have shown as the default text for each field. It is in the Options tab default setting  for each field, and the first column matches what I have specified as the default text for the Company field:

- Select Company -

First Name

Last Name

Building Number and Street Name

Town

Postcode

Telephone Number

Email

Then there are five completed lines of data.

It's KIND OF doing what it should: When the document loads, the Company dropdown box is indeed populated with fro the txt file. The trouble is, this is what it contains:

- Select Company -

First Name

Last Name

Building Number and Street Name

Town

Postcode

Telephone Number

Email

- Select Company -

First Name

Last Name

Building Number and Street Name

Town

Postcode

Telephone Number

Email

- Select Company -

First Name

Last Name

Building Number and Street Name

Town

Postcode

Telephone Number

Email

- Select Company -

First Name

Last Name

Building Number and Street Name

Town

Postcode

Telephone Number

Email

- Select Company -

First Name

Last Name

Building Number and Street Name

Town

Postcode

Telephone Number

Email

- Select Company -

First Name

Last Name

Building Number and Street Name

Town

Postcode

Telephone Number

Email

- Select Company -

First Name

Last Name

Building Number and Street Name

Town

Postcode

Telephone Number

Email

It has the same number of lines as the txt file, (including the fieldnames row), so it knows how many lines there are, but it is pulling data from a complete row, and not from one column. I've altered text in the txt file. in that row, and reloaded, so I know it actually IS doing it dynamically, as the changes appeared seven times in the reloaded page. I've altered things in the code, which has either done nothing, or caused the while dropdown box to display the word "undefined" seven times.

WHat I can get it to do, is display the list of company names, with "- Select Company -" at the top, and, then of course, it needs to populate the other fields on selection of the company name.

I've exhausted all the options that my limited knowledge can provide. I thought I would be able to get there, but although I've come pretty far, I think the final few steps are beyond my current skill!

Llet me know if there is anything wlse you need to know that I have forgotted to add.

Many thanks,

NathanG

[nathang78560588]

Oh, I forgot to add that Trusted_importAddress(); is called on Blur from the Company field actions.

[Thom Parker]

You can give much shorter answers. All I needed to know is that the code for reading in the data file and setting the list items was working, and that the other bit were you import the text data is the issue. Could be because the code is lacking a document reference.

Have you checked the console for errors? 

There are a number of things you are doing in a very non-optimal way. For example, the call to Trusted_readContactDetails should be in a document level script. The Page Open script is run every time the user navigates to that page. The Trusted_importAddress(); function should be called from the Validate script not the On Blur.

[logistics227043683]

Here is some revised code, please make sure to change the variable names to suit your program's needs.

var oFile = util.readFileIntoStream("/Macintosh HD/Users/na***an/Documents/M***EY/So***ts.txt");    
var cFile = util.stringFromStream(oFile, "utf-8");    
var yourCode = cFile.split("\n");  
var i;   
var arrayGet = [];
//We need to get the drop down field where you want to insert the company names.
//Change these values to suit your PDF's means.
var testBox = this.getField("testBox");
//this code is revised for your situation, we have parsed the text file by tab for the
//loop, allowing you to grab only the company name to insert into your drop down box.
for (i = 0; i < yourCode.length; i++){ 
     var tempArray = yourCode.split('\t');
     arrayGet.push(tempArray[0]);
}
//We are inserting the Array of company names into the drop down box
//Change variable names as necessary.
testBox.setItems(arrayGet);
//For the program to change the values of other form fields based on the company name they select
//I recommend using the 'this.getField("fieldname");' method, and creating 
//custom calculations based on what your drop down box's value is.
testBox.setItems(arrayGet);
//For the program to change the values of other form fields based on the company name they select
//I recommend using the 'this.getField("fieldname");' method, and creating 
//custom calculations based on what your drop down box's value is.

I have tested this out, and the entire first column should be inserted into the drop down box. 

As in the code, change the variables, and create custom calculations based on what the value of the drop down box is.

To run a calculation based on whatever company is set in the box, try:

if(testBox.valueAsString == "Microsoft"){
    field_one.value = "Bill Gates";
}

'.valueAsString' is how to check the selected value of the drop down box, and is a good way to run statements in your code.

If you need any more help, ask away!

[Thom Parker]

logistics227043683  wrote
//For the program to change the values of other form fields based on the company name they select //I recommend using the 'this.getField("fieldname");' method, and creating  //custom calculations based on what your drop down box's value is.

I have tested this out, and the entire first column should be inserted into the drop down box.  
As in the code, change the variables, and create custom calculations based on what the value of the drop down box is.
To run a calculation based on whatever company is set in the box, try:
if(testBox.valueAsString == "Microsoft"){     field_one.value = "Bill Gates"; }

Not a good idea.  Setting the field's value in a calculation is less than optimal since the change is driven by a single dropdown selection. The most efficient strategy to use the Validate or Keystroke event on the dropdown to do this.

Since the data that is used to set the field values is acquired from the text file, it would be worthwhile to save that data when the text file is loaded and parsed. That way you minimize external transactions. Using the importTextData function could have performance and reliability issues.

[nathang78560588]

I want to start by saying that I really am very grateful to you for taking the time to provide this code.

I'm very confused, however, about exactly where I am supposed to put all of these pieces of code. You said before, that you would write it as a document level script, but when I try to do that, the "var oFile" and "var cFile" lines throw back an error that I don't have the correct security settings for that, so I can only assume that they are privileged, and need to be called from a trusted function. I; 've never yet been able to make a trusted function work. I don't; know how to call it, how to reference it, where to out the variables (If I put them in the trusted function, at folder level, they are undefined when I try to call the function at document level, and the same is true in reverse. I've tried slicing up the code line by line, and putting some in the trusted function and some in the document level script, but I am really stumped, and I'm genuinely starting to lose my sanity over this.

That's one part of the problem.

I might be misreading your advice about how to get the data to populate the other form fields on selection of the company name.

It sounds like you are saying I have to code the different options manually, but there will be hundreds of options, and having to do that individually negates the point of trying to set this up in the first place.

What I need is for the pdf to read the tab.txt file, and put all of the company names into separate lines of the dropdown box, on document open.

I guess it should also assign the export value of the dropdown box in each case, to match the position in the tab.txt file, so that clicking on the company name will automatically select the correct line in the text file, and load the corresponding names into the relevant fields on the rest of the form. It needs to be done this dynamically, so that when there are additions to the contacts list, I can just update the text file, and the next time the pdf gets opened, it will suck in the updated info, and be able to use it without having to do anything to the pdf.

I hope that makes it clear if it wasn't before.

So as things stand, I can't work out where everything needs to go, and the stuff that I had managed to get even close to working, although throwing a lot of errors at me in the process (that seemed to be different every flaming time!), was loading an entire row of data into the dropdown, and repeating the same data for as many lines in the dropdown as there are in the text file.

I'm literally at the end of my tether.

Thanks for any help that can make this work.

[logistics227043683]

I am sorry, I should have mentioned that the code I have provided will run as an action script:

Action Wizard > New Action > More Tools > Execute Javascript

Do you want the Company names to load into the drop down box every single time the PDF is opened?

[nathang78560588]

And to you, my friend also thanks. You were incredibly kind in starting me on the right track, and engaging with my issue, kickstarting by doing so, no doubt, engagement from others. My path started with you, and I’m grateful.

[Thom Parker · Accepted solution]

Ok, So you are/were on the right track.

1) Yes, the function that loads the names from the file has to be a folder level trusted function, because trust is require for this functionality (unless you don't include the file path). But the correct place to call that function is in a document level script. Document level scripts are called once, when the document is open.  However, this is working already, so I'd suggest concentrating on the part that doesn't work.

2)  Getting data from the selection into the other form fields is another thing. As you've seen there are lots of different ways to make this  happen. You're original import strategy would work, except I imagine that you're getting an error.  But it's fixable. Two other strategies are to load the other data into the export value of the dropdown item, and to load the data into a document level array.  Both of these strategies will be less problematic than the importTextData.

So, use this code as part of the import code

1. var aListEntries = [], cVal;
2. for (i = 0; i < yourCode.length; i++){ 
3.     var tempArray = yourCode.split('\t'); 
4.     cVal = tempArray.shift();
   
5.     aListEntries.push([cVal,tempArray.join(",")]);
6. } 

Use aListEntries as the dropdown entries, the export value is all the other values from the same line in the Text file.

Then on the dropdown selection, use this code in the Validation script

var aValues = event.value.split(",");

this.getField("First Value").value = aValues[0];

this.getField("Second Value").value = aValues[1];

this.getField("Third Value").value = aValues[2];

etc...

Change the field names to match the named of your fields for each of the values.

[nathang78560588]

Hey there Thom,

This is getting close: I'm inching ever nearer!

I'm actually getting quite excited now, as the end is in sight.

As things stand at the moment, I've implemented all of your revised code, and you'll be pleased to know that the pdf now opens with the options of the dropdown box populated with the company names from the TXT file.  That's fantastic, and a big step forward from where I was yesterday.

There's something not quite right with the way the validation script is working, however, as when I select a company name from the dropdown list (fieldName = SelectCompany), It fills the field named "company", which is the first on the validation list of fieldnames,  (this.getField("Company").value = aValues[0];) perfectly well, but all of the other field names from my list, get filled with "undefined".

My list of fieldnames in the validation field of SelectCompany looks like this:

var aValues = event.value.split(",");
this.getField("Company").value = aValues[0];
this.getField("FirstName").value = aValues[1];
this.getField("LastName").value = aValues[2];
this.getField("StreetAddress").value = aValues[3];
this.getField("Town").value = aValues[4];
this.getField("Postcode").value = aValues[5];
this.getField("TelephoneNumber").value = aValues[6];
this.getField("Email").value = aValues[7];

I've played around with it, and setting all of the fields to "aValues[0]" fills them all with the company name, so it seems all of the referencing is correct, but for some reason, the array values of "aValues[]" after position [0] are undefined.

I've checked the options list for the dropdown box, and the export values do indeed contain all the other data, separated by commas, so it' ALMOST there.

Any ideas?

SOOOO close! (Thank you so much!)

[Thom Parker]

You need to do some debug.

Start off by finding out what's being written into the list. Put the form into "Prepare Form" mode and open the dropdown properties dialog. Select the Options tab.  This is where you can check the export values.

One you know the export values are correct, then move the script to the Custom Keystroke, Delete the validate script. Turns out the Validate script doesn't provide easy access to the export values, but he keystroke does.

Use this code

1. if(!event.willCommit)
2. {
3.    var aValues = event.changeEx.split(","); 
4.    this.getField("Company").value = event.change; 
5.    this.getField("FirstName").value = aValues[1]; 
6.    this.getField("LastName").value = aValues[2]; 
7.    this.getField("StreetAddress").value = aValues[3]; 
8.    this.getField("Town").value = aValues[4]; 
9.    this.getField("Postcode").value = aValues[5]; 
10.    this.getField("TelephoneNumber").value = aValues[6]; 
11.    this.getField("Email").value = aValues[7]; 
12. }

[nathang78560588]

SUCCESS IS OURS!

(Well, yours...!)

Thom, I simply cannot thank you enough. You are a genuine genius, but more than that, a true saint!

Its taken a lot of false starts, and countless “try this/try that, oh bu***r, I’ve REALLY messed it up and have to start again” moments, but thanks to your patience, I now have a working pdf, that pulls data from the txt file, displays a list of company names, and when a name is clicked, instantly and flawlessly populates 8 separate fields with the correct info, and I’ve managed to format it all so that once a field has been filled correctly the textSize textFont and textColor all change to say “this one Ian done, move on,“ but if you delete then inputted text, it once again dI splays the drfaiut text, in the original format.

I had to tweak the code a little, as some of my address strings contained commas, and that confused the script a bit as to what should go where, but I changed it to split and rejoin at/with semi-colons instead, and all the fields at first were out by one place, but that was just a matter of adjusting the numbers in the array, so the right slice went to the right field, but I’m actually glad I had to, because it made me investigate, and I understand what the cide is doing a tad better now. And now it’s perfect!

i have someone now inputting about six hundred contacts into a Numbers spreadsheet for me now, which I can then copy into the txt file, answer he countless hundreds of man hours that I will save in the future, as a direct result of your efforts and determination not to let my lack of experience stand in the way of getting a successful outcome on this puzzle, where I no longer have to search around for a customer’s details, or even type them into the form, will always make me think of your kindness and generosity in helping me get the job done. Do you have a ko-fi page or similar that I could stop by?

I really am so very grateful, emotionally drawn, wiped out, deliriously happy, thrilled and excited, and clearly gushing too many words, so if you’ve made it this far, just:

Thank you.

All the best, NathanG

[try67]

In the validation event, event.value returns the display value, not the export value.

[nathang78560588]

Thanks to you for your sage and well-places comments that helped to enlighten and inform  It us appreciated.