Set Design/Searching

[johngtatp]

Here is my dilema, I setup my tables and sets in what I thought was logical but now when I need to search for something, I am at a loss. Perhaps there is an easy way but I can't think of it.

The short of it is:

Main Table of Set: Reservation
Fields: Resnum Tailnum

Child one to many: Resdetails
Linking Field: Resnum
Logical Field: Complete

The reservation has a number which links the reservation details to it. The "Tail Number" (tailnum) of the aircraft is associated with the reservation. People substitute aircraft all the time so my thought was to only store the tailnumber with the reservation then having to rely on cascading changes. As the items in the resdetails file are finished they are marked complete using the logical.

Now, When entering a new record into a set I want to check to see if a particular tailnumber has any resdetails where complete is .f., since tail number is not stored in the resdetails I cannot use an index and thus exist() or dbsum() etc to check. As there are multiple reservations for any tailnumber, it is not as simple as a lookup of the resnum. I hope this makes sense. I thought I would simplify the design and not duplicate info, but now an easy lookup seems out of my reach. Any thoughts?

[glenschild]

Re: Set Design/Searching

Hi John

Have you tried creating a "reverse" set ie: Make Resdetails the primary table and the reservation the child table on a 1:1 link?

Glen

[johngtatp]

Re: Set Design/Searching

Hi John

Have you tried creating a "reverse" set ie: Make Resdetails the primary table and the reservation the child table on a 1:1 link?

Glen

I do this when I am making forms and reports. When creating a new record, I need to check if there are any open (complete=.f.) for a given tail number, the problem with creating an xbasic expression is the tail number is not included in the table to search. Exist() and dbcount() rely on an index and since the field to search is not there it will not work. I cannot use a lookup as again the field to search does not exist only in a relational set. This is my issue that I cant solve.

[Tim Kiebert]

Re: Set Design/Searching

Try this function. I think I have the names right. Save the function in the code tab and then call it where you want to do the checking. The function takes the Tailnum value and returns true if it it finds a false value ie not complete. As the function name implies it returns true if there are any in-completes.

This is basically what Glen suggested but the set is created on the fly. The child(Reservation) is filtered on the Tailnum and then the parent (Resdetails) is filtered on the Complete field. Then just do a count.

FUNCTION Tailnum_has_incomplete as L(Tailnum as C)
	Tailnum_has_incomplete = .f.
	
	DIM tbl_P as p
	tbl_P = table.open("Resdetails",TABLE_MODE_INFORMATIONAL)
	dim tbl_C as p
	tbl_C = table.open("Reservation",TABLE_MODE_INFORMATIONAL)
	dim relation as p
	relation.link_type = LINK_MANY+LINK_MATCH_ONLY
	relation.index_child = tbl_C.order("Resnum","Tailnum = '" + Tailnum + "'","")
	relation.order_parent = "Resnum"
	relation.ref_integrity = REFERENTIAL_INTEGRITY_NONE
	tbl_P.relation_add(tbl_C,relation)
	tbl_P.order("",".not. Complete")
	tbl_P.fetch_first()
		
	dim count as N = 0
	WHILE .NOT. tbl_p.fetch_eof()
		count = count + 1
		tbl_p.fetch_next()
	END WHILE
	
	tbl_P.relation_drop(tbl_C)
	tbl_C.close()
	tbl_P.close()
		
	Tailnum_has_incomplete = (count > 0)
	
END FUNCTION

[johngtatp]

Re: Set Design/Searching

Tim,

Thanks for this, I will give this a go. This is well beyond what I am capable of, so it is much appreciated, I'll report back.

Follow Up:

Worked perfectly, I understand most of it, but I am trying to figure some of the dynamics. It is much appreciated. Its great to see a set created on the fly and then stepped through. It never occured to me. Thanks again, you are brilliant.

[johngtatp]

Re: Set Design/Searching

Tim,

Again, thank you, and it has been working perfectly, but I needed to add another filter and that being ".not. cancel" to what I am counting. I assume I would add that to

tbl_P.order("",".not. Complete") and change it to:
tbl_P.order("",".not. Complete .and. .not. cancel")

but then I started trying to really understand the dynamic of what was going on. This is an order statement, not a filter. I thought this was filtering the on the fly set for only records that are "not complete" and then I looked at the while statement

WHILE .NOT. tbl_p.fetch_eof()
count = count + 1
tbl_p.fetch_next()
END WHILE

and it seems in my limited mind that this is counting all the records, but I know this function works so something is happening in this to make it work and it is not as I think. I thought too that once I get to the first find of a not complete and not canceled record and be done with it. I hope this makes sense, I just am not sure what is exactly happening here. Also the the line:

Tailnum_has_incomplete = (count > 0)

does this change the above variable to .t. if the count is>0? I just didn't understand it as it is written. Thanks again.

[Stan Mathews]

Re: Set Design/Searching

A glance at the documentation showed me

<INDEX> as P = <TBL>.ORDER( Order_Expression as C [, Filter_Expression as C ] )

So the usage Tim provided (tricky and non intuitive) uses the tbl.order() method to filter the table while leaving the order as is.

I would recomend you use it as

tbl_P.order("","(.not. Complete) .and. (.not. cancel)")

to force the evaluation of the logicals the way you want

[Ray in Capetown]

Re: Set Design/Searching

Does anyone know anything about, when to use or reference to TABLE_MODE_INFORMATIONAL
The only place I can find is in the editor when entering table.open mode parameter - no explanation

[johngtatp]

Re: Set Design/Searching

Stan,

Thanks, ill try that, but I was also thinking to make it more efficient, is there a way just to count the number of records in this set on the fly, or as soon as it finds just one record to stop? I do not need to know how many just if any exist that meet the criteria.

[Stan Mathews]

Re: Set Design/Searching

is there a way just to count the number of records in this set on the fly

A5_GET_RECORDS_IN_QUERY() returns a count of records matching the supplied query.

IN the section

dim count as N = 0
WHILE .NOT. tbl_p.fetch_eof()
count = count + 1
tbl_p.fetch_next()
END WHILE

you could

dim count as N = 0
WHILE .NOT. tbl_p.fetch_eof()
count = count + 1
tbl_p.fetch_next()
if count > 0
exit while
end if
END WHILE

[johngtatp]

Re: Set Design/Searching

Stan,

That's very easy actually, I should have seen this, thank you very much.

[johngtatp]

Re: Set Design/Searching

Now a rub...

tbl_P.order("","(.not. Complete) .and. (.not. cancel)")

because "cancel" is in the child table which is tbl.c by reference.

I don't think I can use this field this way, I'll try to play with it.

Later...

I just added another order statement:

tbl_C.order("",".not. cancel")

under the other and all seems to be working now. Thanks for your help.

[Stan Mathews]

Re: Set Design/Searching

Correct, did not read closely enough.

Try changing relation.index_child = tbl_C.order("Resnum","Tailnum = '" + Tailnum + "'","")

to

relation.index_child = tbl_C.order("Resnum","Tailnum = '" + Tailnum + "' .and. (.not. Cancel)","")

[johngtatp]

Re: Set Design/Searching

What do you know, worked like a champ!, Thanks so much.