How to set/move record pointer in child table?

[fsi]

Having searched AlphaPedia for several hours, experimenting to no avail, it's time to reach out.

I've got a set just 4 (1:M) tables deep at max (there are other branches 1 and 2 deep). Each child is appropriately related/linked to its parent.

I want to identify a record of interest in table L3 and, via xbasic, cause the form to "go to" the proper aggregate "record", setting all relevant/linked child record pointers appropriately, displaying appropriate data.

How do I do this? I see plenty of examples for setting the record pointer for the PRIMARY table. But I do not find anything allowing me to set a record pointer for a child table (other than, perhaps, for a "logical record number" - which I do not wish to use). A "find", "fetch" sort of operation seems only to retrieve a record - it doesn't seem to cause any navigation via current record (set) pointer.

The only viable solution seems to be to fetch through the entire set (yes, I can filter on primary table index field to limit the operation) until I reach the child row of interest - not an optimum solution.

Is fetching through the entire set my only solution?

[Ted Giles]

Re: How to set/move record pointer in child table?

You might have tried this but I'd be inclined to create another set with the level you want on top. I hate coding so I look for easier ways to do things.
If it's a simple, and not confidential data set, suggest you post it here and let the Brains dept. have a look.

If it's something like one of my apps with project data, diarised data, risks, issues, who does what in a set, theses can all be displayed on one screen using Browses and cascade the data. But you knew that.

[G Gabriel]

Re: How to set/move record pointer in child table?

Is fetching through the entire set my only solution?

No.. but I am not quite sure I understand the question..
1-You choose a certain child record...how are you going about doing that?
2-Then what do you want to do with it?

Are you having trouble with step 1? or 2? or both?

[fsi]

Re: How to set/move record pointer in child table?

Thank you both for your replies.

In response to the request for clarification re: source of the selected child record: It may come from any number of places... except direct interaction with the user! Think of it as a grab in the air... it might come in via flat file, email message parse, tcp, etc.

Maybe I'd better offer a pictorial (this is a bogus example, for concept only)...

Table "customer" (L1) has pkey and customer name

Table "address" (L2) has pkey, address info, and linking customer.pkey to its parent, 1:M as each customer may have multiple ship-to addresses

Table "dock" (L3) has pkey, dock name, and linking address.pkey to its parent, 1:M as each ship-to will have multiple docks

Table "pallet" (L4) has pkey, pallet source-dest id, and linking dock.pkey to its parent, 1:M as each dock will have received many pallets.

So the relationship is clear. A simple 4-deep set, each table linked to the pkey of its parent.


The Alpha Five form will, of course, display customer, address, dock, and pallet id in a form-type display.

The goal is to make an inquiry against pallet, providing the unique pallet "source-dest" id, and have it display the dock, address, and customer to which it was delivered.

The problem, then, is with step 2.

Yes, I can do a find/fetch against the pallet child table. But the form's current record (or recordS, plural, as it is a set) doesn't seem to get set to allow standard "automatic" display of the entire aggregate record, from the primary table downward. Selecting the child record doesn't seem to trigger a recursive trip back up the tree to set record pointers and display desired fields.


As to creating a second set, flipping the original set upside-down (which I think may be a somewhat common technique), I find that rather un-zen-like. I suspect I'm more likely to code.

[G Gabriel]

Re: How to set/move record pointer in child table?

The goal is to make an inquiry against pallet, providing the unique pallet "source-dest" id, and have it display the dock, address, and customer to which it was delivered.

If you run the query against the form which is based on the set.. it should show all of the above..
What am I missing?

[fsi]

Re: How to set/move record pointer in child table?

I'm happy to read that it should work (easily) as I wish. So it must be that I am the one missing something. I cannot help but suspect I have not found the proper function/method among all of those available.

I've only tried a fetch_find (as it's all I've found that seems reasonable) against the pallet table:

pSet = set.current()
tblPallet = pSet.Pallet
idx = tblPallet.index_primary_put("id")
rec = tblPallet.fetch_find([variable containing pallet id])

fetch_find doesn't seem to recursively set record points. But I haven't stumbled upon another function/method that seems it would allow a direct find against the pallet table and then set all of the set's record pointers appropriately.

Or do I need to do something after the fetch_find (to sort of trigger the form to resynch - which I have tried - against the selected aggregate record)?

[Ted Giles]

Re: How to set/move record pointer in child table?

As to creating a second set, flipping the original set upside-down (which I think may be a somewhat common technique), I find that rather un-zen-like. I suspect I'm more likely to code.

On your own head be it. The whole concept - well for me anyway- is to get applications out quickly which work. Alpha does this albeit with a bit of inventiveness occasionally. Please let me know where to send the Hair Shirt

[G Gabriel]

Re: How to set/move record pointer in child table?

One simple way:
<form>.queryrun()
Many others possible, just not quite sure of the whole context..what comes before ..and after.. but at least this should get you going.

[fsi]

Re: How to set/move record pointer in child table?

<redacted for further experimentation>

[G Gabriel]

Re: How to set/move record pointer in child table?

For a filter, use "Pallet->Id = '000112'"
Specify the order.. even though it says it's optional.. and perhaps it's best to specify the order on the same field you are querying, i.e. pallet->id
Use <cross level>

[fsi]

Re: How to set/move record pointer in child table?

I've experimented with QueryRun() and, while it does more than I had accomplished otherwise, its behavior of setting a true filter is contrary to the goal. I don't want any records filtered out - simply all table record pointers adjusted accordingly, thus retaining the ability to browse to other related (or unrelated) records.

I'll take a stab at coding the behavior I want. Then, if aggravation gets the best of me, perhaps the upside-down set (which I am certain will result in the desired behavior).

Ted - does the Hair Shirt come in XXL?

[Tim Kiebert]

Re: How to set/move record pointer in child table?

jon,

See if this sample does what you want. It only has three levels but you will get the idea I am sure.
The basic idea is to find the target id in the last table. Then recursively search back up through the tables till the parent is reached grabbing the recnos of each table along the way. Then starting with the parent using the record nos to set the form and browses to their respective records. For teh parent we can use .recno_goto(). The browses, embedded or stand alone, don't have such an animal so we use browse1.table_get().fetch_goto() to do it at the table level and then resynch the browse.

It is kind of a long way around and I am only working with small test tables here so it would interesting to know how it performs on larger data sets. No where along the way is it doing any queries. Rather it is doing fetch finds using indexes and then doing record goto.

[G Gabriel]

Re: How to set/move record pointer in child table?

If you wish to see ALL records with the same pallet id, then you need a reverse set (others might call it an upside down, a derogatory term to demonize them, unless if it were a pineapple pie).

I don't know why you resent reverse sets?
It's a self defeating-logic!
You already have a set, don't you?
So, you have nothing against sets, do you?
A reverse set is a reverse of what you already have, what you condone, isn't it?
In fact what you already have is a reverse set of the "reverse set of the the set that you already have", isn't it?
Is like saying heads is opposite of tails. Really? Somebody will argue that is not true, and that tails is opposite of heads!
So which one is the genuine and which is the reverse?

I am just trying to play a lawyer here but the point is, there is nothing wrong with reverse sets. They pose very little overhead. The only reason I don't like them is because I don't like clutter..that's it.

But if you don't want to have a reverse set, an alternative is to load those records in a list or an array and view the list/array in whichever manner you want.

[Al Buchholz]

Re: How to set/move record pointer in child table?

As to creating a second set, flipping the original set upside-down (which I think may be a somewhat common technique), I find that rather un-zen-like. I suspect I'm more likely to code.

I've found that people that write database optimization systems first identify the table that should be primary and then work from there.

Since dbf's don't have a complete optimizer, you need to do this yourself.

Writing the code and not flipping the set, will cause more programming work and longer processing. You'll probably be reading too many records to get the records that you want.

Tim-Nice job (as usual)

[Finian Lennon]

Re: How to set/move record pointer in child table?

Echoing Al, nice job Tim.

Since there is an index for each find I don't think that there would be any perceptible difference in the speed even if the tables each had tens, even hundreds of thousands of records.