Basic Text Parsing

[Arny]

I have, what I believe to be, a relatively straight forward action I need to solve but can't seem to get my head wrapped around the solution.

As an example I have two [2] databases, one call Master the second called Support. I have four unique fields in the Master table named Year, Make, Model, Style as well as a calculated field called YMMS which is constructed as  . . Year_Make_Model_Style . .  [Ex: 1932_Pierce Arrow_Model 38-C_Coupe Runabout].  I put the delimiters in place so I could hopefully parse them when necessary.

My Support table also has these same fields including the calculated YMMS field.

What I want to do is execute a script in the Master database that will initiate a script in the Support database and pass the calculated YMMS field as the script parameter.

Within the Support script I want to retrieve the script parameter and parse the data received, placing the parameter information into each of the four Year, Make, Model, Style fields, excluding the "_" delimiter.

I think I need to approach it this way because FM doesn't let me use the SET script step to populate a calculated field, so . . I figure my best alternative is to populate the source fields that will give me the correct calculated results in the YMMS field of the Source database.

Any suggestion would be greatly appreciated. Perhaps there's an easy way to pass multiple fields within the script parameter, each have multiple words [as shown in my example above]

[comment]

I don't think you need to be doing any of this. What purpose would it serve?

[doughemi]

You really don't want to duplicate the same information in two different tables.

Each record in the Master table should have its own unique ID.  The best way to do this is to have FM auto enter a serial number when it creates a record.

Each record in the Support table will have its own unique ID as well.  In addition, it will have a foreign key field. You will put the unique ID of the Master in that field. When you want to display the make, model, etc. you simply place the related field on the Support table layout.

For example, if Master record number 1 describes a 1932 Pierce Arrow Model 38-C Coupe Runabout, and Support record number 4, 284 is related to Master record 1 because its foreign key field contains 1, then if you want to display the make in a Support layout, place the field Master::Make on the layout.

Edited September 21, 2015 by doughemi

[Arny]

I purposely created separate databases [not tables within the same database] because there are potentially dozens of records in the Support table that relate to the Master table.

Perhaps I didn't explain sufficiently what I need to accomplish [on a 'macro' view perspective], but can't see how the record ID will solve my problem.

I'm using the YMMS [Yr, Make, Model, Style] key as the link between the two databases to ensure that my portal will show all of the related records. I do, in fact, have eight [8] Support tables that I use. I'm using the Master table to provide the user with the ability to select a 'year', which will then display all of the 'makes' that were being sold that year, which will the display all of the 'models' that were available for that make that year and finally select a 'style' that displays all of the styles there were available for the model, for that make for that year. All of this information is contained within the Master database using separate tables within the database to populate the portals in the to assist in the Y, M, M, S selections discussed above.

After this selection has been made, I then use that YMMS key to provide the link to portal views for each of the eight [8] supporting databases. Several of these supporting databases have over 90,000 records and are still growing. Some of these support tables have over 90 entries that relate to a single YMMS key from the Master database.

My problem is that I want to send my Master YMMS key via a script parameter to each of the support tables, which I can then parse through to separate each of the four [4] YMMS values into individual fields within the Support table.

That being said, I do appreciate your input.

[comment]

I purposely created separate databases [not tables within the same database] because there are potentially dozens of records in the Support table that relate to the Master table.

That's not a reason to create separate files. But having the tables in separate files. makes no difference for the purposes of your question.

 

Perhaps I didn't explain sufficiently what I need to accomplish [on a 'macro' view perspective]

No, you did not and it's still not clear. Why don't you start by explaining what does your solution track in real life? What does a record in the "Master" table represent? And what does each record in the "Support" table represent? And, most importantly, why are there 8 of each?

 

I'm using the YMMS [Yr, Make, Model, Style] key as the link between the two databases

This part is the only one I think I understand. If you have 4 fields that need to match in order to make records related, then your relationship should be defined as:

Master::Year = Support::Year
AND
Master::Make = Support::Make
AND
Master::Model = Support::Model
AND
Master::Style = Support::Style

I can see no need to concatenate these 4 match fields into one, and certainly no need to parse them out again. The related records in the Support table already have these values individually - otherwise they would not be related.

 

 

Edited September 22, 2015 by comment

[Arny]

Thanks for helping me realize the efficiency of using the match process to include the four fields . . it certainly does indeed remove the necessity of concatenating the fields to ensure a proper match. Your suggestion will make that interaction between the databases much easier . . thanks.

The separate tables were created to permit, what I thought, would be the best way to isolate the detail tables from the client user. The intention is to make the solution available over the web using a server solution. The client side would only refer to the Support tables but not be able to access the databases directly.

The entire solution consists of three separate segments.

Segment 1 The Master database keeps track of every Year, Make, Model and Style of vehicle manufactured in the U.S. since 1898. The tables within the Master database permit me to identify a specific combination of permitted YMMS data. I.E. if I enter 1858, every make that existed in 1958 will be displayed . .no others. When I select one of the displayed makes, only the models built by the maker will be displayed . . no others and when I select one of the models only the styles available for that particular model will be displayed . . no others. This four part selection process will uniquely identify the vehicle in which I'm interested.

Segment 2 The support tables are now linked to the master record using the proper relationship setup . . your suggestion makes this much more straightforward and easier.

Segment 3: I have a client database that permits the client to have portal access to the Master table and create their own selection of YMMS information. This database also has portal access to all the Support tables so the client can view every Engine option, Interior color option, Exterior color option, Production volumes, Brochure images, Vehicle photographs and Misc information that relate to the selected vehicle.