Are the location codes in the same position within the texy strings? Or do you have a table where the location codes are identified so that a relationship can be made? sorry was on mobile when i replied.
if you have a table with location code you can create a relationship between the flight table and location code table
flight table may need an additional field to isolate the loc code since the code can be either 1 or 2 characters in the text string
FlightTable::LocCode = LocTable::LocCode
Flight Code Date Loc Code DepartDate
01/07 KAAA KBBB 1/7/17 K =Date + (LocCode::Days)
01/13 KBBB KEEE 1/13/17 KB
01/13 KEEE KBBB etc... K
When you're coding, anything is possible.
However, as currently described, you have too much behind the scenes "magic" logic that determines date ranges. I would seriously recommend a more normalized database approach with clearly defined fields.
So, for example, if "K" means 7 days, then have "K" in a separately linked table with "7" as another clearly named field. In that way, the logic is much clearer.
Having lots of implicit logic for complex field names as in your example above is a recipe for lots of problems (bugs). Plus, if any database person has to maintain your software, they will probably have problems understanding it, too.
So, as with many questions asked here, the issue about proper database design will solve most of your problems. Check out the DB design course on Lynda.com and invest a couple hours. You'll probably be glad you did.
Thank you very much for your comments.
Yes, I do have a table with location codes that I have added to the DB (same URL).
I am a neophyte regular user of FMP with no coding experience so I am not sure how to implement what you suggest. Would it be possible for you to give me detailed instructions or perhaps work on the file itself?
Thanks for your response.
K does not mean any number of days. It refers to a geographical location.
I have no control over the structure of the DB. I am forced to work with what I have.
Unfortunately, I prefer not to touch people's files. By Trade I am not a computer guy and/or a FMP guy and would hate to mess something up.
As for setting giving instructions. if you can provide example tables and related fields and relationships with a few entries of data each to show a representation of what you are trying to achieve. I could help walk you through it, or even better someone may have a better solution.
1 of 1 people found this helpful
Case ( Left ( Origin City ; 1 ) = "K" ;
Date - GetNthRecord ( Date ; Get(RecordNumber) - 1 )
This calulate days on each record. (On first record it can't be calculated, then return '?'.)
You can add them making summary field for K.
Your data have some error that
first 4 records don't match Origin City and Destination City in previous record
Flt# 14779 records have older Date than previous record
Your solution worked like a charm. Much appreciated. I have sent you a private message.