Converting Works DB to FM 8.5

[dbCAN]

This is my first post and I am totally new to FM.. though I believe it is the solution that I will be quite happy with once I get over a learning curve.

My problem is that I have an existing db that is used for timecards. It includes first and last name, date and 6 fields each for Project Number, Job Number, Time and OT.

I manage to do a direct import into FM with by creating a table with the same fields. But I am trying to figure out how to do a report broken down by Project Code and the sub totals of the Job Codes (Time is in Hours, OT is in Hours) in that project. Since the data in each Project Code field for each record can contain any project Code how can I generate a report that would cycle through each of the 6 Project Code fields for all of the records and their related data?

Also, the Project Code fields get filled in starting at Project Code 1, then Project Code 2 etc. This means that sometimes only the first Project Code fields will have values and the rest are empty.

Any ideas are appreciated.

regards,

[Ender]

Hi Glen,

Your question seems to be more about building a report than importing or converting.

It would help us to know more about the fields and tables involved.

[dbCAN]

Well I guess it would be more about building a report (I thought that it may be a problem with the way I have the data imported).

Currently I have one table TimeCard. It contains the following fields:

Date

First Name

Last Name

Project Code 1

Project Code 2

Project Code 3

Project Code 4

Project Code 5

Project Code 6

Job Code 1

Job Code 2

Job Code 3

Job Code 4

Job Code 5

Job Code 6

Time 1

Time 2

Time 3

Time 4

Time 5

Time 6

OT 1

OT 2

OT 3

OT 4

OT 5

OT 6

The Project Codes are 5 digit number, Th Job Codes are 4 digit numbers. Time and OT are single digits with a decimal.

An employee submits a card that he fills in the the project codes that he worked on and the job codes that describe what he did for that project... of course Time and OT is the amount of time spent on each activity.

Since the employees all wor on different projects and don't really care what order they describe their day.

I need to create a report that breaks out each project by job code subtotalling Time and OT per project.

Hope this clears things up a little better.

My original post had me thinking that if I could create a secondary table based on Project Code then the report would be easy but creating the second table was what I struggled with.

[Ender]

Yes, you should add a Project table with fields Project Code, Job Code, Time, and OT. To link it to the TimeCard, add a TimeCard ID field to both tables. The TimeCard ID in TimeCard will be the primary key in that table and should have an auto-entered serial number. The two tables would then be linked via their respective TimeCard IDs.

With this structure, you can use a portal in a layout based on the TimeCard table for view/data entry.

Your report would be in a layout based on the Project table. You can make a columnar report with sub-summary parts for Project Code and Job Code. Use Summary (Total of Time and Total of OT) fields placed in these parts to calculate the sub-totals. When the records are sorted by Project Code and Job Code, and viewed in Preview Mode or printed, the report will show those sub-totals at each break.

[dbCAN]

Ok so this is somethingthat I thought of (sort of but with some missing details).

I understand the linking of the tables with an auto generated serial id for TimeCard but I am uncertain how:

1. the different Project Codes ( 1 - 6) get migrated to the new Project Table. If there is one Project Code field in the new table how do I link the individual Project Codes? This is where my lack of FM experience comes in...sorry but that's why I am trying the forum for help...to learn.

2. the empty Project Code fields are handled...via script? or some parameter setting?

regards,

[dbCAN]

I appreciate the suggestion of the Project Table but I am still hoping that someone will answer my question on how to actually do this as an example.

Again, I am new to FM and need a little more help with the solution.

thanks,

[Ender]

1. the different Project Codes ( 1 - 6) get migrated to the new Project Table. If there is one Project Code field in the new table how do I link the individual Project Codes? This is where my lack of FM experience comes in...sorry but that's why I am trying the forum for help...to learn.

2. the empty Project Code fields are handled...via script? or some parameter setting?

You would Find and Import the records from each Project into the Project table in separate steps. So:

1. Find the records in the original file that have a Project Code 1,

2. Import the Project 1 fields into the Project table,

3. Repeat for Projects 2-6.