Set-Link by Expression

[johngtatp]

I have never used link by expression for a set before and was wondering in the following situation if it can be done, should be done, and would it help my situation.

I have a table that should have both an arrival record for an aircraft (designated with an "A" in a field called "type") and a departure record (designated with an "D" in a field called "type"). I say should because sometimes when the parent reservation record is created only the arrival may be known and thus there would only be one record of an "A" type and no "D" record created yet.

I have a need to compare many items between the arrival to the departure, for example to see what the time difference is between the two, or if a service was done on the arrival. I have been using filtered indexes and calculated fields such as:

'Checks the existence of the departure if the record I am on is an arrival
existd = if(type="A",exist(ALLTRIM(STR(RESNUM)+"D"),"res_detail","dupentryd"),.f.)
'looks up the reservation date (rdate) for that departure
existd1 = if(calc->existd=.t.,dtoc(lookupd("F",ALLTRIM(STR(RESNUM)+"D"),"rdate","res_detail","dupentryd"),"1-"),"")

to get the data that I need. As there is quite a lot of this going on, the form is painfully slow. I would like to access the record directly and I was wondering if I could do this with a filtered set somehow.

This means making the parent and child with the same table.

I was thinking this would greatly speed things up. It is the same table which I thought would create issues, but I tried linking it on a one field parameter and it was allowed, and it worked. It did not work as intented, of course, but it did show me two records from the same table on one form.

I did try a simple IF statement in the set linking and it did not work, but I wanted to ask about this practice, OR IS THERE A BETTER WAY. If so, how should the set link expression be constructed. I have never used this before and I was hoping for some sage advice.

[DaveM]

Re: Set-Link by Expression

well, My first thought is to take the work out of the index and place it in a funtion or a script. Function is usually better depending on what is done. It would only be activated when the departure of "D" was entered in a form.

answer a question, I have a set where a table is included in a set 2 times(the second instance is an alias), but neither is the master in the set. It has performing excellent for years.

I have a db right now that I am repairing and redoing. The previous programmer placed hard functions in the field rules and nearly stopped the app. there are(won't be later) many filtered indexes, very long table names and index names, haphazard sets, oversized memo files. Lots of work for me. No security on a very delicate app for information. Looks like it was slapped together in a week of part time work, many shortcuts in the work.

I have another one coming up that is worse, if I get the job. Used 5 tables where 1 would be more efficient and memo files are almost 2 gig.

[MoGrace]

Re: Set-Link by Expression

Have you got another table that you could use as the header? Because this sounds like a general ledger scenario where you might want to link the same child table twice to show debits from the first and credits from the second. The 2nd instance of the child would then use the alias as Dave suggested. I don't think making the same table also a child of itself would work as you would like.

Then you could do something for a set like - using each child with a different filter:

PLANES
==>ARRIVALS ' TYPE = "A"
==>DEPARTURES 'TYPE = "D"

[johngtatp]

Re: Set-Link by Expression

Dave,

Having used alpha, very part time, for years, I have always struggled and learned through each project. Speed and design were never an issue because all of my projects have been small single user types. Being what I would call, a hack at this, makes me dangerous, but I do continually try to learn and do this better through the board. This current project was a huge undertaking, over my head mostly, that was written part time over the course of a year. I can think of many different ways to redo what I have done, but I am at a point where I am trying to make it work.

Robin,

Perhaps I can do this. This form shows all of the records for a given date. I might try this. Your analogy to a ledger is very close so perhaps this is an idea.

LATER:

I tried to set up the set with a parent and have one be the child and the same file to be the child of itself and that does not work. It says the file is in use. I tried in the linking field:
if(Type="A",str(resnum)+"D",if(Type="D",str(resnum)+"A",str(resnum)+type))

The common field is a reservation number, they each have the type field which is different. I need to display as a schedule, so I querry by a variable date to show a schedule for that day, but I need to reference the previous arrival or the future departure as for calculations and color fields. I was trying a "dummy" as the parent. I could not have each of them as a child as i need to have a one to one for the record and that is why I was trying to have it be a child of itself. I am not sure where to go with this.

[MoGrace]

Re: Set-Link by Expression

John,
I have an old copy of your project from 2011 - I am sure you have progressed from the origninal design since that time - but it was interesting even then.

I also have an old project of mine for a trucking/ warehouse company where a default 'master' list of charges is in one table and then for each customer, charges are selected from that list and assigned on a per customer basis. These were the normal fees to be charged each month while their goods were in house. Thus the charges eventually became the line items for the invoices and could be 'batch' selected for monthly billing while still allowing for 'extras'.

It almost sounds like you are doing something similar whereby each 'tailnum' has a specific combination of services only they might utilize.

[johngtatp]

Re: Set-Link by Expression

Yes, similar indeed. The main point to all of this was the status board, where each service has an associated color that is on multiple displays throught the facility. I originally wanted to do each service as a one to many to the aircraft reservation. I could not easily figure out an efficient and quick way to look through all the children for the 12 different services and look at the status for each (up to 4 possibilities eah), then color code a cell in an embedded browse, so I have one record for the arrival and one for the departure, and that record has all of the services within it. I can easily pull up a daily schedule and have all of the services easily color coded in a browse.

[spudmurphy]

Re: Set-Link by Expression

If my understanding is correct you have a master table of planes (flight numbers) and a detail table of plane activities - I assume that there is a unique identifier for a plane/flight and on the detail records a related date for a specific group of activities ( a flight) for that master UID, so on the detail table the overall master to detail identifier is plane(flight id) and date. If that is the case then the details should be structured as plane/flight id plus date plus sequence number say 0 for type "D" and 999 for type "A" (both created at the same time) followed by detail record time stamp - then every detail record sequence number created in between would be incremented as last sequence plus 1. It would not matter if the time of arrival was unknown in this scenario as it could be updated when known.

Set would be :

Master (plane/flight id)
contents: plane/flight id
PLUS whatever else

One to many LINK to detail would be: plane/flight id = plane/flight id
providing groups by: date + sequence no. + Type

Detail (plane/flight id activities)
contents: plane/flight id
date
sequence no.
type
PLUS whatever else

For example:

Plane\Flight Number: "BA1234"
Description: "Boeing 747 Reg1234"

Plane\Flight Number: "BA1234"
Date: 12/12/2012
Sequence: 0
Type: "D"
Time: "1220"
Departure Time: "14:30"
Arrival Time:
Other Detail: ????


Plane\Flight Number: "BA1234"
Date: 12/12/2012
Sequence:1
Type: "R" (Refuel)
Time: "1235"
Departure Time:
Arrival Time:
Other Detail: ???

Plane\Flight Number: "BA1234"
Date: 12/12/2012
Sequence:999
Type: "A" (Arrival)
Time: "1535"
Departure Time:
Arrival Time: 1800
Other Detail: ???

etc.

[DaveM]

Re: Set-Link by Expression

Before we all get confused(me mostly)
what Robin meant and I eluded to is a header table. It is a table to connect the others to. It does not even have to have records in it, just the fields used for connection.

as:

myheader.dbf
id1 connects table a
id2 connects table b
id3 connects table c

my header.dbf is what you would build the set on.

[johngtatp]

Re: Set-Link by Expression

Dave,

I tried this finally and have a header table (reservation) which has the Reservation Number (resnum) in it. I set this as the master table of the set and tried to link in a one to many link the reservation details (resdetails) twice; it is linked by the common resnum. I put a filter on each child tables, Type = "A" and Type ="B" for the other. When I try to open the default form or use the set in any way I get:

Error loading: Error adding relation child: The process cannot access the file because it is being used by another process.

Any thoughts?

[Ray in Capetown]

Re: Set-Link by Expression

John
Dave's suggested method is solid and efficient. Works every time.
Problem here being to picture what you expect this to look like.
There, for you to get the principle and make a working design.
Here's a working example. The only form - Flightform

[johngtatp]

Re: Set-Link by Expression

Ray,

Good morning I will look at this, thanks. This is what I was asking to do, but mine will not do that, I have to play with this some.

[Ray in Capetown]

Re: Set-Link by Expression

That single record single field table is all you need.
Then add you flight table twice, linked as the example.
If struggling then send me your chopped down flight table or set (zipped using the right click, zip - and the form will be included)

[johngtatp]

Re: Set-Link by Expression

Thanks Ray.

I am working on this. I was trying to link it with just the resnum, then using a filter to restrict it and that is what is throwing the error. So perhaps this will do it. Thanks so much.

[Ray in Capetown]

Re: Set-Link by Expression

I never try filtering child tables - too much headache - this just uses fetch_first() when the link field changes

[johngtatp]

Re: Set-Link by Expression

I tried setting it up. I got the error. I then set it up using a local copy with no one else on the network and it works. I then took the set definition and returned to the network enviornment and tried to open the default form and got the attached error: warning.jpg

I should be able to open a default form no matter if there are many users, right?