Imported Records

[-bc-]

I hope I'm just suffering from sleep deprivation because I know this is incredibly easy to do BUT, I can't for the life of me make it work. Here is the situation: I collect my daily travel miles using an iphone app (milebug). At the end of the month I just email myself the report and, up until now, just parse it in Excel and use that to mail merge into a word doc. A lot of steps just to print out a report but I have to have it in the company's format. So, I figured I'd slap a quick FM database together and that way I could could easily keep the information better organized if I need to do something with it later.

Anyway, Simple database, 3 tables

USER - user name, address, etc (needs to be on the report)

TRAVEL - this is basically a join table but I want to be able to store records by months (or by the period I submit them)

TRIPS - the imported travel data

So USER and TRIPS both contain key fields that tie into TRAVEL

So I import the data (manually at this point, will be scripted later) into TRIPS, and it displays fine in my Trips layout.

I go to my Travel Voucher Layout (records from TRAVEL) and put in a portal for the TRIPS data and I just can't get it to display.

Anyone have any idea what I'm probably doing wrong?

TRAVEL also contains a Key fields and I tried separate table occurances linking the TRAVEL keyfield back to Trips foreign key.

ARGGG, tried, show all records, sorts, nothing

[comment]

So USER and TRIPS both contain key fields that tie into TRAVEL

How exactly?

[-bc-]

USER Table contains _kp_user_id which is a serial # field Text

TRIPS Table contains _kp_trip_id which is a serial # field Text

TRAVEL contains _kf_user_id and _kf_trip_id

_kp_user_id connects to kf_user_id

_Kp_trip_id connects to _kf_trip_id

I also created a _kp_travel_id (serial number field) in travel and a corresponding forign field in TRIPS and, using a separate table instance of TRAVEL I connected kp_travel_id to TRIPS _kf_travel_id (did this to associate all the trips to the _kp_travel_id just to see if that would help.

[comment]

I connected kp_travel_id to TRIPS _kf_travel_id

Having a foreign key field in in the Trips table is not enough; you must also populate it with a value matching the primary key value of a record in the Travels table.

This is assuming one Travel has many Trips - it's not quite clear from your description. If that is true, then the relationship:

_Kp_trip_id connects to _kf_trip_id

makes no sense.

[-bc-]

Well, that is essentially my problem. I can't figure out how to automatically assign the _kp_travel_id (from the travel table) to each _kf_travel_id in the TRIPS table. After the import all _kf_travel_id values should be (or be equal too) the single _kp_travel_id. There must be a way to have that assigned automatically. People import records all the time. It would be nuts if they have to jump through hoops to then associate those records with other data. Do I need to write a separate script to assign each value? Or, maybe make _kf_travel_id a calculation field? Hmmmm, going to try that.

[comment]

I am still not clear on what you are trying to accomplish here.What exactly is the TRAVEL table? You say it's a join table, but it doesn't seem so. What does a record in this table represent in real life?

People import records all the time. It would be nuts if they have to jump through hoops to then associate those records with other data.

Well, the real question here is: does a record in TRIPS contain enough information to determine which record in TRAVEL it belongs to? If yes, then the process can be automated.

I am also not sure about your USER table. Are there any other users beside you? If not, you don't need this table. If yes, then the TRIPS table must also identify the user.

[-bc-]

Easiest way is to look at it.

I've attached 2 files. One is Travel.fp7

The other is milebug_report.xls which contains the date I wish to import.

The intent is to start with the USER layout, enter user name (could have set USER up as globals, probably will only ever be used with one person)

Next is to go to the Travel Voucher layout just to create a record to create a key field record to associate all the trip records with. I then go to the TRIPS layout and do a file/import and import the records from the xls file.

Thanks for your help. Sorry that I'm not very clear at explaining things.

Well, that didn't work, guess I can't upload filemaker files. If you want to shoot me an email I can send you the file, or upload it somewhere and give you a download link. Is there a way to send PM here? Hate publicly posting email addresses.

Bruce

[comment]

guess I can't upload filemaker files

You need to zip them first.

[-bc-]

Its attached (I think)

Thanks

Travel.zip

[comment]

My question - what does a record in the TRAVEL table represent in real life - still stands. Your file shows each record as an individual trip, so it seems like a pointless duplication of the TRIPS table.

Perhaps all you need to do is to import the trips, and at the end of each month find the trips of the month and submit them.

[-bc-]

Well you are close, and I see what you are getting at but TRAVEL table represents a group of trips that I submit for payment. It MAY be a months worth of trip records but it might be 2 months if I didn't travel much or a couple of weeks if I traveled a lot. It it represents a grouping of trips much like an invoice represents a group of line items submitted for payment.

[comment]

Ok, let's say you have a Reports table and a Trips table. Any time you wish to submit a report, create a new record in the Reports table and fill out the start date and the end date of the report. The trips falling in that range will become automatically related by using a relationship:

Reports::StartDate ≤ Trips::Date

AND

Reports::EndDate ≥ Trips::Date

You just need to make sure your report periods do not have overlaps or gaps - you could probably use a script to create a new report and populate the start date with the last report's end date + 1. This also assumes you won't be importing trips whose date falls in a range already reported.

[-bc-]

That is an excellent solution and probably what I will end up doing now that you mention it. BUT, at the moment I'm obsessed about why what I tried to do doesn't work.

[comment]

I believe it didn't work because you missed a crucial step. You are right that a report is like an invoice. But ordinarily you create the invoice first, then enter the line items into a portal. This way each line item is automatically tagged with the correct InvoiceID. Here the order is reversed: you have the line items before you have the invoice. So after creating a new invoice (report), you must grab its ID, find the items (trips) you want to include and populate their foreign key with the grabbed value.

[-bc-]

That's what I kind of thought which is why I created the 2nd table occurance to feed the set the primary key first. BUT... Okay, at least I know what is going on. I do like your solution. It makes a lot of and simpler is better.

Thanks for the help.

Bruce