Grid Type Table how to Import and relate.

[Baylah]

Greetings,

I know there is a way I can "brute force" the data to get what I want but everytime I do that I find out there is much more elegant way to do it properly. In this case though I am completely stumped and I am wondering if one of you Uber_FM-guru's can help me out.

The Image below Is from a much larger "grid type" pricing matrix that is supplied to me in Excel. I have attached the Excel file to this post as the image below is not very clear. The data is on the tab titled V-Class.

Column and Row Definition:

©DEST = a Destination City

©Three letter identifiers = Departure city.

®Season = a travel Season

In FM for a user to be able to enter the Destination and Departure with the result being the FARE in the four seasons.

So, If:

DEST = ATH

Departure Code = BOS

Low:$529

Shoulder:$685

High: $1104

Peak: $1154

[image]http://www.data-base-solution.com/images/Airfare_Grid.jpg[/image]

Is there a way I can do this as a relationship where the grid is part of one SUPER Record (If I remember right there is a max number of fields on a record, no matter what)? Or do I need to create (thousands or records) with a field that will apply a unique ID to each possible combination (combine DEST+DEP = ATSSBOS) so my user can input two fields and then the look up will do it's thing? If that is the case, would anyone know how to automate creating the Unique ID's? This is a very large grid and would take forever long hand.

This table changes once a year so I am trying to figure out the best way to have a managble way to update this table on an annual basis. Especially if it winds up being a using creating the update instead of me.

Thanks in advance for any help. This would be huge!

Steve

Fares.zip

Edited August 5, 2008 by Guest

[Baylah]

No takers...This is a first!!! All kidding aside though; can someone who is more experienced than me just let me know if this is a high difficulty task? It seems like it to me as I have not had any success getting anywhere on my own yet. I don't want or expect anyone to do a lot of work for nothing but I don't know where else to turn for help. Should I be looking for "help for hire?"

I have hard coded about half the table by hand (easy but tedius). And while it works perfectly this table needs to be updated about once a year and if I am out of the picture I was hoping to give my client a more elegant solution.

Thanks again,

Steve

Edited August 7, 2008 by Guest

[Vaughan]

One of the challenges with Excel files like these is that the data is often partly summarised so it is optimised for display. (This is often called a cross-tab report and it's not how FMP and other database systems can natively display data.) Unfortunately it then becomes difficult to import the data into some other system like FMP.

What you'll have to do is first work on creating a file to convert the data in the Excel file into a format that FMP can work with. This will involve parsing out each city column and converting it into rows. The records ultimately need to have fields:

Area, Destination, Season, Departure, Cost

With records containing data like:

Europe, AMS, Low, NYC, 373

Europe, AMS, Shoulder, NYC, 551

Europe, AMS, High, NYC, 840

Europe, AMS, Peak, NYC, 890

Europe, AMS, Low, BOS, 411

Europe, AMS, Shoulder, BOS, 572

Europe, AMS, High, BOS, 620

Europe, AMS, Peak, BOS, 670

Each record needs each of its fields to contain data. In the Excel file, some fields (like area) have been merged to make them look nicer; when put into FMP this data needs to be re-inserted. The smallish table you posted above will result in 4x6x3= 72 records, hence the need to create a file to automate the conversion process.

It isn't hard but it's a bit tricky and will require quite a few loops. Once this converter file is done and working, it can be reused each time the information changes.

In the original post you're concerned about creating the unique Ids. FMP does this automatically, it's the least of your worries.

[Baylah]

Hi Vaughn,

Thanks for taking the time to respond. You bring up all of the points I was having difficulty with. And, most importantly the output you suggested is exactly what I need. However, I have not been able to write the looping scripts to parse the data as you suggest. Since this is something I need to get done I have been doing this manually...while it is tedious it is working.

Your output looks like this…I just need one addition and then everything in my solution works perfectly.

HAD TO EDIT OUT PART OF MY RESPONSE TO VAUGHN AS STATED MY NEED INCORRECTLY. I WILL REPOST IN A LITTLE BIT. I APOLOGIZE FOR THE CONFUSION.

I have included an FMP file with the raw data. If anyone wants to take a stab at the looping scripts Vaughn recommends it would be hugely appreciated.

Thanks,

Steve

Fares.zip

Edited August 10, 2008 by Guest

[Vaughan]

"I have not been able to write the looping scripts to parse the data..."

A lot of this depends on taking the time to play around and see what works and what doesn't, to try and save some time and effort later on.

I'd start by simply converting the Excel file to a FMP file (just drag and drop it) and see how the data looks. It might be a mess and useless, or it might be a really good start. If it's a mess then it's going to be a *lot* of work which might start off with modifying the Excel file to make the conversion work. But be careful; it might be cheaper to pay somebody to re-type the data into the FMP final database than bother with a converter file.

[Baylah]

Hi Vaughn.

Attached is an FMP file I have been working that has the RAW Excel Data cleaned and transferred into an FMP table. Also is part of the working DB application which shows the result I am trying to achieve. While this is a small part of a much larger project the big picture will not work without this piece.

I have been playing with all kinds of different looping scripts without any success so I reached out for help. Since I wasn't able to get any I started entering the pairs by hand. While it is not diffiuclt it is tedious.

I only want to learn the better way to do it so in the future if I run up against this again I will know how to handle it.

I'm still trying to get it but I am at a point where it is more expedient to manually enter the pairs. I will keep on this until I get it right but the bigger picture project is under a deadline so I need to keep moving forward.

Steve

AirPairs.zip

[Baylah]

I figured it out! If anyone wants to know how let me know and I will post the looping scripts that made it work.

Once I finish prepping the Excel table I will attach the FM file with the raw data and scripts so anyone who cares can see how it worked.

Steve

[Vaughan]

Well done. :

[The Gardener]

Hello,

I would be very interested in seeing, learning, how you accomplished that.

Thanks