working with a data array in a single field

[Brian C]

I am attempting to make use of a data array contained in a single field. (a multikey)

The field contains data that is return delimited.

How do I go about:

1) finding a specific match to a particular line in the array

2) determine the line number of the matching line

3) return that line so i can parse out the result and do something with it

[QuinTech]

Hi, Brian. It sounds like this field is more than a multikey, it is a mess. I would only use multikeys in situations where the data is non-discrete (if that's the right word); that is, a good multikey would be:

a

ap

app

appl

apple

apples

A bad multikey (going by this theory) would be:

apples

bananas

cherries

In the first example, each of the keys is, basically, the same thing; in the second, they are very different things. I could be way off base, but i can't think of a good reason to do that. If you are doing it, then it may be that you need to rethink the whole structure.

Is it possible for you to explain (specifically) what you want to do? It could be that your example proves me wrong.

Jerry

[transpower]

Brian: In version 7 we would use the RightValues and LeftValues functions to extract what would be needed. In version 6 you could use a repeating field, which allows a find for any repetition. You could write a calculation function which would step though each repetion (for instance, using Case) and then return the repetition number.

[Ugo DI LUCA]

I would largely agree with Jerry that there might be a structural "problem" here.

However, I was intrigued and played a bit with it.

Unfortunately, I've just realized you were running 6 so there may be something to change for this to run.

At least, it can be done. You can find which row of a multiline field have a match in the related record, and then pick from a list which row you want to use

arrayRelationship.zip

[Brian C]

My multikeys are made of of fixed length data which is concatinated in order to make each key unique.

From this I am trying to see if i can easily extract a specific line after determining a match to grab a portion of that line to plug into a process.

[Brian C]

A multikey is used in relationships, and therefore each line entry has to be unique.

Example format for each line in the multikey:

ssn(9), payee type(2), payment type(2), payment subtype(2), entry date(6)

I am aware of how i can parse data from a line once i have it.

I know I can check for an occurance of an entry in a multikey using the Position() function, but beyond that I need to be able to step/loop thru the lines and extract a line so that i can parse it.

I do not want to use a repeating field because it is a not a standard way of storing data. It is a FileMaker only data storage element. Nothing I develop will ever use repeating fields simply because it only complicates the process of conversion if the need ever arises.

[Ender]

So, if I read you correctly it looks like you are building your multikey out of different types of fields (ssn, payee type, entry date, etc.) I'm afraid this does not make much sense to me. Is this for some sort of search portal or something? Then why is the matched line important?

Perhaps you can share the big picture of what you're trying to do.

[Brian C]

Sometimes the Why is not as important as the How since the How will help us all to expand our skills.

After staring at this problem for some time and taking a closer look at the available text functions, I think this could be possible if I use:

Patterncount() to determine the number of return characters in the field.

Middle() to build a loop to extract the information using the number of carriage returns found.

I will post the results of my findings once I have the specifics worked out.

[Brian C]

The reason why the multikey is necessary is to keep things optimized instead of having to create duplicate records. Payees link to their payments, and payments to their amounts. Dependents and Beneficiaries must link back not only to payees, but also to specific payments. The multikey is the way these dependents/beneficiaries link back to the specific payements.

There are a multitude of things I could accomplish by using this process, and not just for multikeys. The ability to manipulate an array of data in a single text field/global field can be very powerful for even temporary purposes.

[Brian C]

The reason why the key I use is created from many fields is because I have to make each payment record unique. To go any further into this would require me to begin explaining the entire payroll system and how and why it works in the manner it does. I dont believe I need to go into this level of detal in order to solve this particular question. Suffice to say that this key carries over to many tables for tax and history purposes. Matching keys are needed for data to roll into the correct related payment records. I do not have the option of changing this key to something else since the enitre system and all the reports etc are built off of it. The client relies on the fact that this system will continue to work the way it did on their old platform.

This system was originally written in cobol. Due to the complexity of the system and limitation on time, I did not have the opportunity to analyize it and re-write it, only to translate/create and get it working as quickly as I could before the deadline.

This system has taken roughly a year to complete as is almost done. So what I have is what I am stuck with until I have the time to begin conversion to 7 later this year.

Perhaps this sheds a little light as to the why. I appologize if I seemed vague or short.

[Ender]

An array data structure is typically represented by a repeating field not a portal. This is because 'arrays' are accessed by their cell or row number. Relationships don't usually work this way.

I would suggest that the example you have provided would be best setup with separate files for Dependants, Beneficiaries, and Payments. Keep the parent-child relationships simple and use separate relationships using compound keys to relate parent to grandchild.

BTW: The "Why" becomes important when we can't seem to figure out what you're doing. This usually indicates a design problem (or a communication problem.)

[Brian C]

Each of the items I am referring to are already stored in separate tables/files and linked/related appropriately.

This design of this system has been working for the the past 10 years, so it is a sound design.

I have about 20 years of experience in working with databases and relationships on various platforms so I'm not new to this. But I do appreciate your advice.

[Ender]

Each of the items I am referring to are already stored in separate tables/files and linked/related appropriately.

So what is the multikey used for?

Sorry, it's still not making sense. Can you be more specific about how this multikey fits into your relational structure?

[Ugo DI LUCA]

First of all, I confess I have no clue about what you're trying to achieve with such an array parsing through relationship and multikeys.

Then again, repeaters are what is closest to an array and using repeating fields doesn't mean you'd ever store data in them.

I have no time to rebuild my example file to fit the 6 format but you'd have seen that no other info than the occurrence and position counts are stored in the repeating field.

As you can build value list from a repeating field, you'd therefore get a list of those lines in your key that match the related data.

The only limitation to this system is the number of "

[Brian C]

I believe I have managed to get it working in 6. I will post more details when I am more awake.

While you can test a relationship from a single key to a multi key, it is a different thing altogether to know if every key stored within a multikey has a valid relationship in the opposite direction. Upon testing each of the entries, I can then substitute() to remove a key that is no longer valid. I can also take this key, parse it out and use the information to create new records, produce a custom adhoc report of errors, etc.

As for repeating fields, understand, when I say I cannot use them, I cannot use them because of documented standards I am supposed to be following in my development.

In any case, I am sorry if I have managed to irritate anyone. It was not my intention. I admit I got a little defensive and I appologize.

My original thought was to pose the question and hope that someone already had the answer at their fingertips. It was not my intent to make anyone go thru any hoops to answer this question.

Complex text parsing in other programming languages is a pretty commonplace thing and is generally required the deeper you get into creating file formats among other things. This was just the first time I found a need to begin exploring this direction in Filemaker Pro.

Even if this is not the preferred or best way of doing what I am attempting, it is none the less something that I need to know more about in order to be more proficient in FileMaker. Hense my comment regarding the "How" being more important than the "Why". Which I think was not taken in quite the spirit in which it was intended.

In any case, thanks for respoding to my post.

[Brian C]

Sometimes I wish I had a do-over for those times that I seem to stick my foot in my mouth and wind up swallowing it in the process...

[QuinTech]

No biggie, i think we are pretty flexible here. Speaking for myself, i think i have probably learned more from this site than i have taught, and that includes this thread.

I do find your multikey system intriguing. I know i objected to it initially, but when i look at what you're actually doing (the triumph of the "Why" over the "How!" ), it looks like a pretty good idea. I still think that multiple types or multiple objects in a multikey is a bad idea, but there is a sense in which your multikey contains one object, just several iterations or incarnations of it. I could see how this would be useful in a "what-if" scenario, where you want to look at several variable-dependent options.

You'll find, if you haven't already, that Filemaker is a very unique program in many ways when compared to other DBMS. That's both good and bad. But its uniqueness is what makes us always ask the "Why" before the "How"-- it may appear to us that you're trying to make lemonade from oranges, when orange juice would satisfy your needs just as well. Or something like that.

J

[Ugo DI LUCA]

Hi Jerry, Hi Brian,

Yes indeed this was intriguing.

I actually already did some of this stuff to find exact match in a multikey built from choices. I think it is somewhere digged in the Forums, and was a scripted process looping through related records after multiple CopyAllRecords.

Then, I played with this approach and repeaters with 7 and felt like this could be a good way too.

Brian, as far as Text Parsing goes, you may find a tutorial (at least a starter one) in the Article Section. May be this may help a bit.