Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Searching and matching a field value and returning a different value in same row

Explorer ,
Aug 13, 2020 Aug 13, 2020

I am trying to create an Adobe form, but am being asked to automate something I'm not sure is possible.  

So here is what the form looks like:

 

Capture.GIFexpand image

The Monitoring Well Elevations Recorded from has two choices -- top of casing or ground surface.

The Tripod Location has 5 choices Loc 1 - 5.  Benchmark colum - they can enter whatever they want.  Type has 3 choices (in picture).  Fore-Sight and Stick-Up are entered by user.  Elevation GS and TOC columns have calculations.

 

Here is what I want to do, but have no idea how:

  1. When a new “tripod location” is selected, it will always be designated as a “turning point” “type” in the first row of that new location.  It will also always be associated with an existing “benchmark” from a previous surveyed location.  In the case above, the first row of “Loc 2” is associated with benchmark “MW18-02 TOC”, which was one of the benchmarks from “Loc 1”.  The “height of the instrument” here needs to be automatically calculated based off the following:

 

IF “Turning Point” is selected in the “Type” column, AND “TOC” is selected in the data entry cell for "Monitoring well elevations recorded from" then:

        1. The “Height of Instrument” will be calculated by the equation: [Previously calculated “TOC Elevation” of Turning Point benchmark] plus “Back Sight”
        2. For example, if you are using MW19-04 as your Turning Point, you must use the calculate TOC elevation for MW19-04 in your calculation of the Height of Instrument

      IF “Turning Point” is selected in the “Type” column, AND “GS” is selected in the data entry cell for "Monitoring well elevations recorded from" then:

        1. The “Height of Instrument” will be calculated by the equation: [Previously calculated “GS Elevation” of Turning Point benchmark] plus “Back Sight”
        2. For example, if you are using MW19-04 as your Turning Point, you must use the calculate GS elevation for MW19-04 in your calculation of the Height of Instrument

 

Therefore, in the example below, the “height of instrument” for “Loc 2” would be: 99.800 + 1.990 = 101.790

 

So even with the form fields being named Benchmark.0 and Benchmark.1, etc., is it possible to have the values in that column searched to see if one of the fields above it equals the same value as the current field and then can you access the value from another form field in that same row and use that value in a formula?

 

I am really new at creating Adobe forms -- this is only my 4th one and by far the most complex with the calculations.  I am also attaching the form as I have it so far so you can see the calculations and validations created so far.

 

Any advice or guidance would be very much appreciated.  

1.7K
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 13, 2020 Aug 13, 2020

This is the Using the Community forum (which is the forum for issues using the forums).
Please tell us what Adobe application you are using so that this can be moved to the proper forum for help.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Aug 13, 2020 Aug 13, 2020

Sorry -- I'm new to this and am having trouble figuring out our site.

 

This is for Adobe Acrobat Pro DC form designs and Javascript programming.

Thank you!!!

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 13, 2020 Aug 13, 2020

Moved to the Acrobat forum.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Aug 13, 2020 Aug 13, 2020

I moved your post over from the poorly named "Using the Community" forum, which is for getting help using this forum system, to a better forum.


I hope this helps. Best of luck to you.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 13, 2020 Aug 13, 2020

The answer to your question is Yes!  But to do this your fields must follow a consistent naming convention. The calculation script for a field will  need to be able to predict the names of the fields it needs for a calcultion based on it's own field name.

 

So, in the calculation script the name of the calculation field is "event.targetName". 

You should read the free articles linked here:

https://www.pdfscripting.com/public/PDF-Form-Scripting.cfm

and see this article on splitting and rebuilding strings. This is the technique used to create new names from the name of the existing field.

https://acrobatusers.com/tutorials/splitting-and-rebuilding-strings/

 

The calculation should actually be a document script that will work for any line, so all the field names will need to be automatically generated. But for now work on writing out a description of the calculation using relative field names (like: if "tripod position on same line" then ). Then you can convert that to javascript code for the real calculation.    

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 04, 2020 Sep 04, 2020

Hi Thom.

So I reviewed the articles you attached and have a couple questions.

The logic is:

Height of instrument will = same Benchmark value as is in the current row.  Example: if that benchmark field = Benchmark.2 then I need to get the value of GS.2.

 

So presuming I am currently in row 4, I would need to search through rows 1 - 3 for a matching Benchmark value as the current row.  So how do I write that?  Do I need a for loop?  


And I'm still not understanding how to have it search for the Benchmark value through the Benchmark.# codes only.  

If this.getField ("Benchmark.4").value == varSearchingforMatchingBenchmarkValue [not sure what to put here] {

  var IHope= MatchingBenchmarkValueNumber.split(".").pop();

  var Finally= this.getField("GS" & pop()).value;

  event.value= (Finally+VarBackSight.4);

}

 

Am I close?  Just not quite sure I'm understanding the process yet.  Thank you again so much for your guidance 🙂

 

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 04, 2020 Sep 04, 2020

So, I'm not really sure about the specifics of the calculation you want to do. But lets just say that for a calculation script for a field in a particular row. Is the the row number part of every field in that row? I'm going to assume it is. So here is how you look at the "Benchmark" values  for the current row, and all previous rows.

This code is for a calculation script in one of the fields on the row

 

//First get row number. IT is assumed to be the last part of any field name in the table
var nRowNum = Number(event.targetName.split(".").pop());
// Get Benchmark value for current row
var cBenchmark = this.getField("Benchmark."+nRowNum).value;

// Loop through benchmark values in previous rows.
for(var i=1;i<nRowNum;i++)
{
    cPrevBenchmark = this.getField("Benchmark."+i).value;
   // Do something with value
}

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 16, 2020 Sep 16, 2020

Hi Thom.

 

Thank you so much for this!  I think I understand most of the code here, but have one more question.  

maverik_1_0-1600294927818.pngexpand image

 

 

 

I am starting at "0" for the numbering, so Benchmark.0 is row 1 Benchmark value. 

 

So I get up to the for loop and this will loop through the benchmark values? 

So in this example, I am in Benchmark.3 (the last row) and it needs to match to Benchmark.1 (the second row). So does the code:

  cPrevBenchmark=this.getField("Benchmark."+i).value   

mean that it will match Benchmark.1 to Benchmark.3 and change the +i to a "1"?  

 

Or do I need to do an if statement inside the for statement to the effect of

  if cBenchmark=cPrevBenchmark {

    var cTOC=this.getField("ElevTOC."+i).value;}

   event.value = cTOC+Back.3

}

 

Sorry, I'm really new a javascript and am not really understanding.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 16, 2020 Sep 16, 2020
LATEST

You are on the right track! 

And yes, the "if" is needed for identifying the row with the matching benchmark value. In the code below the calculation value is set to the "ElevTOC" field (+ something) in the same row as the matching benchmark.

Notice the "break" statement. Since the value is being set in the "if" block, the code needs to exit the loop. No need keep looking through the rows. Thus assumes of course that there is only one matching line.

Notice also that I changed your "Back.3" to "something". This is because "Back.3" doesn't make any sense. What is this supposed to be?

 

var nRowNum = Number(event.targetName.split(".").pop());
var cBenchmark = this.getField("Benchmark."+nRowNum).value;

for(var i=1;i<nRowNum;i++)
{
    cPrevBenchmark = this.getField("Benchmark."+i).value;
    if (cBenchmark=cPrevBenchmark) {
       var cTOC=this.getField("ElevTOC."+i).value;}
       event.value = cTOC+something;
       break;// leave loop
    }  
}

 

BTW: my discussion and the structure of the code has little to do specifically with JavaScript. The structure of the solution would be the same in any programming language.  

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines