For the Love of God Someone Help Me

[sfericean]

Ok...here we go...

1) I apologize in advance for my ignorance.

2) I have excel row data with multiple rows but one key "emp_id". (I've attached an example "sample.xls" under "source" tab)

3) I want my source data to look like my data in my "sample.xls" under the "desired" tab when I import into FM.

4) I'm using FM PRO 10 Advanced

5) I'm on a PC

6) I'm pretty decent with DB's and FM. I'm not a walking DB brain but I get what's going on with DB's & FM most of the time.

7) I feel stupid because I can't understand how to get my data to import the way that I want it.

8) Somebody please help.

9) Please be kind; and if there is a post that already exists with this exact request, please let me know, because Lord knows everyone is doing something like what I want but apparently no one is doing exactly what I want.

10) Once again I apologize for my ignorance.

Sample.xls Document

[comment]

I would suggest you rethink your strategy, because the arrangement you show as "desired" is HIGHLY UNDESIRABLE (to put it mildly). You should import the source data as is into a table (CreditCards?) related to the Employees table by matching emp_id. Then you can show the information in a portal to CreditCards placed on a layout of Employees.

This way each employee can have ANY number of cards without requiring additional fields, and you can find employees with card "abc" by searching one field instead of five.

[sfericean]

Thanks for the quick reply...

I agree...and I have my main tables setup the way you said where there is an infinite number of cred_name's related to the one emp_id.

(Now this is hilarious) But despite a proper DB setup it is imperative that I can show that same row data in a columnar form like in my "desired" tab...

Is there a way to make that happen? If not its cool. I just need to know if there is a quick way of doing something like that because my source data is always in row form like my "source" tab in my sample document. (Because we have an old piece of crap DB as our HR system that only exports in row form)

I'm trying to import data from our current HR system into FM to help organize it better and make report running or presentation of data faster. Right now we export data out of the current HR system and then move data over and delete lines in Excel...needless to say that's a bit of a pain when we have 1000+ employees all with 3-7 credentials per employee...that is constantly changing which if forgot to mention. So we can't just run one report move all the lines and be done with it...data is updated from the Department of Education on a weekly basis via an upload file directly to our HR system.

If I lose the ability search/sort via cred_name that's ok because everything we do for this portion of data processing/presentation is specific to the emp_id not the cred_name (AND, I can always run a quick query out of our piece of crap HR system and sort/search cred_names if I ever needed it quickly)...hope that make sense.

And once again thanks for the quick reply and help...

Quick Edit: my source can be independent of my Main DB...essentially if i started with a clean slate just source data like in my example can I make it change to my "desired" tab style of presentation, of that source data.

Edited August 2, 2010 by Guest

[bruceR]

Should be easy enough with side by side filtered portals in FM11. Are the dates really as you show - or do you have month/day switched?

[sfericean]

Just example data..guess I got all excited about January...but yes it comes out in that fashion mmddyyyy

But you mention side by side filtered portals in FM11...I guess I need to get the district to upgrade my license....

[comment]

I am not sure I understand the problem here. If the data to be imported arrives as shown in your "source" tab, then you don't need to do anything - as it is already in the ideal format to import into Filemaker.

If you want to produce a report with the credentials arranged horizontally, that's another matter. Is this report to be printed out from Filemaker, or do you want to export it as a file to be used by another application?

[sfericean]

Correct...it is in the ideal format to import into FM...now once in FM is there a quick computer way (as apposed to moving lines by hand in excel) to make my imported data be presented in a similar fashion as what is in the "desired" tab of my sample.xls file?

It's crazy, I know but that's what I'm talking about...the closest thing I was able to come up with was the attached file.

If you look at the layout "Credential Report - I" its the closest representation to what I'm looking to do but a closer representation is getting all the data on one line...

Maybe it's just not possible and the layout/report that I created is the closest thing to doing something via computer quickly that's pretty close that doesn't require a lot of manual work just a quick upload...

Wow sorry for all the headache...

Click to download FM Sample File

[comment]

is there a quick computer way (as apposed to moving lines by hand in excel) to make my imported data be presented in a similar fashion as what is in the "desired" tab of my sample.xls file?

There are several ways to do that - here's one that's very simple (make the portals invisible after you get them the way you want them).

Teacher_Credentials_Sample.zip

[bruceR]

"Correct."

*Which* is correct?

The question was, do you want this to be printed; or viewed; or exported for use with some other app?

[sfericean]

Wow I am dense... :

Multiple portals with the initial row changed to something else...shear genius for you...and the sensation of feeling like a goober for me. :

Thanks man that is exactly what I'm looking to do...now if I may be so bold as to ask..(since you sparked my interest)

Quoted from previous post "Is this report to be printed out from Filemaker, or do you want to export it as a file to be used by another application?"

Ok last question and I promise I'll stop bugging everyone..can you re-export that data into some type of file type (one that can be opened by excel preferably) with the data in horizontal fashion?

Reason being is that we have some excel die hards that want raw data in .tab or .xls formats but they don't want to do any of the leg work or go on any data basing forums and look like an idiot asking stupid questions on horizontal reports and finding out that it was something super easy all along...

Thanks in Advance!!!

[comment]

can you re-export that data into some type of file type (one that can be opened by excel preferably) with the data in horizontal fashion?

I can think of three ways:

1. Define five calculation fields in the parent table for each column to be exported =

GetNthRecord ( Child::Field1 ; n )

with n increasing from 1 to 5. Then export directly from the parent table.

This is a tedious hack, but it's easy to implement.

2. Have a script compile the entire report into a global text field as tab-delimited table. Export the field contents.

3. Export as XML, using a custom XSLT stylesheet to reformat the output. This is the best option as it requires zero resources in the Filemaker file.

[sfericean]

Sheesh...I need to change my "intermediate user" level to "beginner user" level.... :

Thanks for everything you all are the best...it's all starting to make sense...

I'm going to use option #1 for now since I need a quick fix; but I'll explore the other two methods also...

Heck everything I've learned in FMP has been trough trial and error and asking others, and reading forum posts...I'll keep plugging away and keep working to getting better results and creating better data bases.

Thanks for all the help,