Jump to content

How to decide which related record to use?

This topic is 6639 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I am trying to import data from one table to another.

The purpose is to produce an export file in a preset format for use by another application.

The big table called CR_1 is flat and used for the actual data export ... it contains the rules that apply to airline fares and are related to a number of smaller tables by one field (ContractKey) and when needed by a second matching fieldthat each contain various related parts of the data.

For example a contract has various seasons of validity ( anything from 1-40 possible periods per contract) This data is held inother table CR_Season and related to CR_1 by the field ContractKey.

Each record also has a season code which can apply to more than one season and is used to group seasons together within each set of records with the same ContractKey.... for pricing purposes..... season code L might have 6 periods, S has five and H has three. Each season code will be priced as a whole.

CR_1 is formatted for the export of the data and contains eight fields for entering season ,one period per field. Since an individual record in CR_1 will contain only one price it means that only those seasons which apply to both the main contract ID and a specific season code are required.Therefore a second field "SeasonCode" used along with ContractKey to find the related records . Due to the rigid structure required for the data export there is can only be one season entered per field and no duplications of season. Limiting the number of CR_Season records sharing the same ContractKey and SeasonCode has been limited to 8 unique periods.

All the above works fine.....

My problem is how do I write the calculation for the season fields in CR_1 to say.....

for CR_1::season1 get the first record from CR_Season where both contractkey and seasoncode match the record in CR_1

for CR_1::season2 get the second record from CR_Season where both contract key and seasoncode match

........etc etc for all eight possible fields

I can get the set of applicable records easy enough but the bit about saying specifically ...use the first related record from this relationship or the second one or the third one is what is making me scratch my head...... I know I could have used repeating fields (and may have to if I cant work this out) but I prefer not to if possible

Link to comment
Share on other sites

Create a another TO (Table Occurrence) for CR_Season and another TO fo CR_1. For these tables, have two links in the Relationships Graph, instead of just one--contractkey and seasoncode. Then, from CR_1, you can pull the information from CR_Season that you need. Be sure to use the correct tables and fields.

Link to comment
Share on other sites

Maybe i am not totally understanding how this works

Gathering the info for a single season is ok...

What I end up with multiple records (betwen one and eight) where seasoncode and contract key match .....all of which I wish to use in CR_1

So I have the eight records from the season table I want... all with the same season code and contract key. I need to place all eight of these records into separate fields (one per field) within one record in CR_1

CR_1 has eight fields to enter this data into...... and what I am trying to acheive is to place one of these records in each field in CR_1 without duplication.

For example

Using a ContractKey of 12 and a season code "L" I get eight records from the CR_Season table each with different dates. Each record in CR_1 has eight fields for this data.... season1, season2 etc.........to season 8... one field per related CR_Season record

I am trying to find either calculations or lookup definitions which places the first of these records in the CR_1::season1 , the second in CR;;season2 ... all the way to the eighth applicable record from CR_Seasons in CR_1::season8 field

Link to comment
Share on other sites

You could create a value list based on the related field you want to extract. Then each field can use an unstored calculation of

Substitute( MiddleValues( ValueListItems( Get(FileName); "YourValueList" ); N; 1 );

Link to comment
Share on other sites

This topic is 6639 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.