Data pull/format

[Matthew R White]

How do I pull certain specified data using filemaker calculation engine

For example see below, I'm trying to pull the word Cessna, from line 4 and Model 150L from line 5?

 

Thanks!

 

 

Start of Aircraft Description

Aircraft Description

Serial Number 15072812 Status Valid

Manufacturer Name CESSNA Certificate Issue Date 10/26/2010

Model 150L Expiration Date 10/31/2016

Type Aircraft Fixed Wing Single-Engine Type Engine Reciprocating

Pending Number Change None Dealer No

Date Change Authorized None Mode S Code (base 8 / oct) 50150223

MFR Year 1971 Mode S Code (base 16 / hex) A0D093

Type Registration Individual Fractional Owner NO

Start of Registered Owner

[comment]

Here's one way you could look at it =

Let ( [
prefix = "Manufacturer Name " ;
suffix = " Certificate Issue Date" ;
start = Position ( Textfield ; prefix ; 1 ; 1 ) + Length ( prefix ) ;
end = Position ( Textfield ; suffix ; start ; 1 )
] ;
Middle ( Textfield ; start ; end - start )
)

If Textfield contains the text in your question, the result will be "CESSNA".

[Matthew R White]

Thanks! So... I tried Go To Text Field, then Insert Calculated Result with that calculation to another text field and got no result

What am I doing wrong?

[comment]

What am I doing wrong?

 

I don't know. If you want to put the result in another Text ( i.e. not a Calculation) field, then Id suggest you use the Set Field [] script step.

[Matthew R White]

What do I do with this calculation? Do I use it to sort the data in the current field - which would exclude all other data, or do I use it somehow to export the data / filter it with this calculation and then enter the result into another field?

[comment]

What do I do with this calculation? Do I use it to sort the data in the current field - which would exclude all other data, or do I use it somehow to export the data / filter it with this calculation and then enter the result into another field?

 

Why don't you tell us exactly what you want to do, and for what purpose? I assumed that you have a Textfield containing all the text in your original post and that you wanted to extract selected data from that field into dedicated fields. If that's so, you can define a calculation field named cManufacturer and specify the above formula as the calculation. Or, you can define a Text field named cManufacturer and use a script to set it to the result of the same calculation. Use the second option if you plan on changing the extracted data at any point and/or if you want to delete the original Textfield after the extraction.

[Matthew R White]

Ok... I have an Aircraft Insurance business, I'd like to set it up so that my employees can click a button within filemaker after entering in an N Number into a field, filemaker then navigates to http://registry.faa.gov/aircraftinquiry/NNum_Results.aspx?NNumbertxt=1852Qvia a web portal, I would like filemaker then to copy that into a text field (all of this works so far) then disperse the specified data into about 10 different fields.

This would save us a HUGE amount of time! The whole first part works great, the data has been imported to filemaker and I've got the data to a text field. I just need help sorting it and sending it to these 10 fields

[comment]

I would like filemaker then to copy that into a text field (all of this works so far)

How exactly do you copy "that" into a text field?

 

 

IMHO, you would be better off scraping the HTML source of that web page.

[Matthew R White]

I have a script for copying it to a text field:

Go to Object (web viewer(

Select All

Copy

Go to Field (web viewer text)

Past 

 

Please, if you know a better way...

[comment]

Have a look at the attached file - I believe it's a bit more robust method. Ideally, they would have an API that would let you import the response directly - but in the absence of that...

ScrapeFAA.fp7.zip

[Matthew R White]

I've looked at the file, its seems a bit complex to me.

Isn't there a simple way to just say Go to this field, in this field find the word after prefix = "Manufacturer Name " ; and insert that word into field A. Then find word after prefix = "Suffix " ; and insert that into field B?

[comment]

Isn't there a simple way to just say Go to this field, in this field find the word after prefix = "Manufacturer Name " ; and insert that word into field A.

 

I gave you this simple way in my first reply.

[Matthew R White]

So I'm asking how do I apply calculation that to get text from one box to another?

[Lee Smith]

Here is a sample of comment’s calculation used in Scripts.

Matthew R White.fmp12.zip

[Matthew R White]

Lee, THANK YOU SO MUCH!

The method is now clear to me! However I have also now figured out the problem with why it didn't work. Using the snippet of text I posted on here as a sample it works, however using the full text I get from the site, including another header called Serial Number at the top of the page, it doesn't seem to work.

 

Please see the full text posted below.

 

 

 

Aircraft Inquiries

 

N-number

Serial Number

Name

Make / Model

Engine Reference

Dealer

Document Index

State and County

Territory and Country

Pending / Expired / Canceled Registration Reports

Recent Registration

N-number Availability

 

Request A Reserved
N-Number

Online

In Writing

 

Reserved N-Number Renewal

Online

 

Request for Aircraft Records

Online

 

Help

Main Menu

Aircraft Registration

Aircraft Downloadable Database

Definitions

N-Number Format

Registrations at Risk

Contact Aircraft Registration

FAA Home» Licenses & Certificates» Aircraft Certification» Aircraft Registration» Aircraft Inquiry»N-Number Inquiry

MENU MASTER BEGINS HERE

 

 

<div id="alert"> <table class="item"> <tr><td class="warn">JavaScript support is required by this page.</td></tr> <tr><td class="item">View <a href="NoJS_Instructions.aspx">information</a> on how to set your browser to accept JavaScript.</td></tr> </table> </div>

 

FAA REGISTRY

N-Number Inquiry Results

N1512Q is Assigned

</br><strong> JavaScript appears to be disabled. JavaScript is used to display processing date. </strong></br> Enable JavaScript to view date.</br>

Data Updated each Federal Working Day at Midnight

 

Start of Aircraft Description

Aircraft Description

Serial Number 15072812 Status Valid

Manufacturer Name CESSNA Certificate Issue Date 10/26/2010

Model 150L Expiration Date 10/31/2016

Type Aircraft Fixed Wing Single-Engine Type Engine Reciprocating

Pending Number Change None Dealer No

Date Change Authorized None Mode S Code (base 8 / oct) 50150223

MFR Year 1971 Mode S Code (base 16 / hex) A0D093

Type Registration Individual Fractional Owner NO

Start of Registered Owner

Registered Owner

Name GROVES FENTON A

Street PO BOX 205

   

City MORRISVILLE State NEW YORK

County MADISON Zip Code 13408-0205

Country UNITED STATES

Start of Airworthiness

Airworthiness

Engine Manufacturer CONT MOTOR Classification Standard

Engine Model 0-200 SERIES Category Utility

  A/W Date 09/14/1971

The information contained in this record should be the most current Airworthiness information available in the historical aircraft record. However, this data alone does not provide the basis for a determination regarding the airworthiness of an aircraft or the current aircraft configuration. For specific information, you may request a copy of the aircraft record at http://aircraft.faa.gov/e.gov/ND/

Start of Other Owner Names

Other Owner Names

None

Start of Temporary Certificates

Temporary Certificates

None

Start of Fuel Modifications

Fuel Modifications

None

Deregistered Aircraft info Back to top button, New Search button and updata data message

Data Updated each Federal Working Day at Midnight

 

End of Center Div Container for page

MENU MASTER ENDS Both or Either the Horizontal or Vertical XSL menus can be used in tandem

 

The footer div encapsulates everything at the bottom of the page proper. Include items meant for the footer before the matching </div>

U.S. Department of Transportation

Federal Aviation Administration

800 Independence Avenue, SW

Washington, DC 20591

1-866-TELL-FAA (1-866-835-5322)

Readers & Viewers

MS Excel Viewer

Web Policies

Web Policies Notices

Privacy Policy

Government Sites

DOT.gov

USA.gov

Plainlanguage.gov

Recovery.gov

Regulations.gov

Data.gov

Frequently Asked Questions

Aircraft Registration FAQ »

All Questions »

Contact Us

Contact Aircraft Registration

OIG Hotline

FOIA

[Lee Smith]

That’s the problem when scraping web pages. There are so many variables that can mess you up.

 

My first thought was to ask you to post a link or the raw data from the site so that we could see what you were seeing.

 

Originally I thought you were just coping a section of a page that  would stay static, I posted my file as  the simple method.

 

Even after looking at the Link and your post #15 I think I would just do it that way.

Edited March 18, 2015 by Lee Smith
changed text

[Matthew R White]

Ok, so how do I skip to the second instance of the words (Serial Number) because as it is now I'm getting caught up in the first instance.

[comment]

Ok, so how do I skip to the second instance of the words (Serial Number) because as it is now I'm getting caught up in the first instance.

 

Have a look at what the occurrence parameter of the Position() function does.

[Matthew R White]

Using the script:

Let ( [

Textfield = BWIA2 FMP11 2::Web Viewer Text ;

prefix = "Name" ;

suffix = "Street" ;

start = Position ( Textfield ; prefix ; 1 ; 3 ) + Length ( prefix ) ;

end = Position ( Textfield ; suffix ; start ; 1 )

] ;

Middle ( Textfield ; start ; end - start )

)

 

I get the right data but there is a huge space before the data and multiple blank spaces after the data. Any way to fix this? I used the same code in the MatthewRWhite file example wherein there aren't any spaces yet getting spaces in my file

[Lee Smith]

I get the right data but there is a huge space before the data and multiple blank spaces after the data.

 

What white space?

 

The Position Function should have eliminated the white space if it is showing in the field.

 

Why don’t you post a copy of your file so we can see first hand what you are seeing?

[Matthew R White]

Lee,

Please see file attached, thanks!

Matt

Matthew R White.fmp12.zip

[Lee Smith]

Hey Matt,

 

Try removing (deselect) the checkboxes in the Web Viewer.

 

Also, you should create a Loop for the Scripts so they get populated all at once.

 

If you want me to, I can make those changes and post back?

 

Lee

Oops, I just looked and see you already made the script for the loop.

[Matthew R White]

Lee that would be fantastic! The faa number box is going to be used by my employees and there are a few things I'm still struggling with

First of course is the extra spaces before the text

Second, I will hide the web view and text box from the interface, not sure if we need to keep that

Third, if an employee enters an n number then hits Faa data bull button before the site has finished working the data won't pull correctly. So I was looking for a way to inactivate the gas pull button until the site has loaded or show an indicator that turns green once the site has loaded

Thoughts?

[Matthew R White]

Lee, any thoughts on removing the White Space?

Thanks

Matt

[Fitch]

Trim?

[Lee Smith]

Hi Matt,

 

Why are you posting multiple topics? I read in another thread that you wanted to know where to find the rules.  You agree to them when joining the FM Forums, and most of that appears  in the topic labeled Site instructions,  that contains the Site Terms of Service. There should be to link to How-to-post in it.

 

I don’t see a response to my previous post?

 

I’m going to be out of the office this afternoon, so I’ll try to help you out later.

 

Lee

 

 

[Matthew R White]

Thanks Lee, any help would be greatly appreciated.

 

Fitch, would I apply the Trim Function in the data pull script or as conditional formatting to each individual text box field?

[Fitch]

Trim the data.