Jump to content

Data pull/format


This topic is 2808 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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".

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Lee Smith
changed text
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

This topic is 2808 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.