Advice - Working with an array

[johngtatp]

I have a browse on a form that has 14 columns with color equations and almost all of them have conditional formating as well. The conditional formatting has 2 possibilities which are both loookupc() statements. This is alot of looking up although it is with an index. The record each row's 14 or so columns need to look is the same record but different fields. I was thinking, which is dangerous for me by the way, could I read an entire record (or the fields I need) into one calculated field one time (some kind of array or cr-lf delimeted item), that I could then somehow reference the appropriate field's data for my conditional formatting fields and it would not have to relookup the data 28 times.

1. Would this be a better or faster way to do this?

2. Is this even possible? (I have no idea how to do this)?

3. Any Hints?

Thanks in advance.

[G Gabriel]

Re: Advice - Working with an array

So, each "Cell" in this browse has a conditional formatting? Or just the columns?

[johngtatp]

Re: Advice - Working with an array

It is for each column. Each record in the browse has another record in the table associated with it. I have to compare values against the two records. Various symbols and colors show up to indicate the comparisons. I tried in the set to attach the same table with filters but it is very unreliable. I thought if I could easily read in the entire record or the fields I need that match the same reservation number in this case. Then all of the color expressions and conditional formatting could refer some how to a calculated field instead of doing a bunch of lookups.

[Ray in Capetown]

Re: Advice - Working with an array

John, firstly you want to try

dim r2v as P
dim tbl as P
tbl = table.open(mytbl)
tbl.record_to_vars(r2v)	'1.Copy record to var r2v

Is this a case where your reservation periods are across the top and the items down the left?
Then you will want individual cells formatted according to reservation status and linked to reservation record, no?

[johngtatp]

Re: Advice - Working with an array

Ray, your idea looks like what I was thinking. Question, how do you refer to the fields contained in the variable? Also, how can I run this xbasic for a calculated field, would I make a function out of it and call it from a calc field?

The table being displayed is a schedule with each record in a browse time in ascending order. Each column are the services for that airplane. There are 2 entries for each aircraft, an arrival and a departure. The services could have been done on either the arrival or departure or both. I have to compare each line item of the Day and it's companion. I also need to know the days between.

What I have been doing is using the color background of the column to indicate the status of the item. If the item was done or worse not done, on the companion arrival departure, I have put in a symbol to indicate both of these possibilities. I have a lot of lookups going on and it's pretty slow. I am not sure which is the worse culprit the color equations or the lookups. I need the color because it is the heart of the system and there is no other program out there like it. I know I am a newbie sloppy coder and I want to know find more efficient ways of making it happen.

[Ray in Capetown]

Re: Advice - Working with an array

I can help work it out for you but need to know
1. are field names in each row the same name as the fieldnames you are looking up?
2. If each record in the browse (I presume that means row) is time then the planes (x2 Arr/Dep) acros the top.

Instead of referring to a field from table.read you refer to the pointer variable with the fieldname like r2v.fieldname
But will need more as above OR a sample browse to prepare an example.

[johngtatp]

Re: Advice - Working with an array

Ray,Thank you for looking at this.

are field names in each row the same name as the fieldnames you are looking up?

Yes they are the identical record. Each record has a reservation number and a Type, the Type will be an "A" for arrival and a "D" for departure. They are 2 records in the same table and obviously then have the exact same information.

If each record in the browse (I presume that means row) is time then the planes (x2 Arr/Dep) acros the top.

See the attached screen shot. The main screen is based on a date and shows each record in time order. The columns are information about that arrival/departure and all the services that have been requested or have been done. What was happening is services were being duplicated because frequently the arrival and departure do not occur on the same day and most of the services could be done at either time and is a preference of that crew. If a service has been done on the arrival a green checkbox is shown, worse if a service was requested but not not done I show a red exclamation point. All I do is lookup the previous status of the arrival record, but I do this for each service. If I can easily reference the associated record, it would be faster I assume

schedule.jpg

[Ray in Capetown]

Re: Advice - Working with an array

So if it is an arrival entry with that reservation numer you want to behind the scenes check the departure record?
And vice versa. And compare the viewed line values against its opposite number.
Lets say for simplicity, the fields are called TIME,STAGE, CLIENT , RCAR
1. You open the underlying table (in xbasic). Can keep it open for the duration, for better speed. Using the sample code snippet, table name has been saved in a variable called mytbl
2. run that code after locating the partner record. Declare the variables first. The field values are all saved to R2V pointer variables
3. When you determine conditions for colors etc, compare TIME.value on the form against variable r2v.time, and STAGE.VALUE to r2v.stage, client.value to r2v.client etc

[johngtatp]

Re: Advice - Working with an array

Yes exactly. I would compare one to the other and I would use that to populate the browse. I had no idea you could do this.

[johngtatp]

Re: Advice - Working with an array

How do I find and set the pointer to the record with the open table, I would have the reservation number and if it was a "D"eparture I would look for the "A"rrival, but I have not used xbasic to do this before. I assume I can use table get since it is open already. Do I have to close the table then? But, since I have to do this for each line item in the browse. Should this be a formula that is then called by a calculated field?

EDIT:
I am trying to use the tbl.fetch.find here, i'll post back...

So what I tried was first creating a function:

FUNCTION getarrdep AS A (vresnum AS N, vtype AS C )
dim global r2v as P
dim tbl as P
dim indx_1 as P
tbl = table.get("res_detail")

if vtype ="D"
indx_1 = tbl.index_primary_put("dupentrya")     'this sets the index on a filtered index which  is made out of the resnum + "A"
rec = tbl.fetch_find(alltrim(str(vresnum)+"A")) 'I am trying to find the resnum + "A" for arrival using the variable passed to the function
tbl.record_to_vars(r2v)	
end if

if vtype ="A"
indx_1 = tbl.index_primary_put("dupentryd")
rec = tbl.fetch_find(alltrim(str(vresnum)+"d"))
tbl.record_to_vars(r2v)	
end if

END FUNCTION

Then I put a calculated field on the form:

test = getarrdep(RES_DETAIL->Resnum,Type) ' I thought this would force the function to run and pass the two variables which make up the reservation number and Type (Arrival or Departure)

I then put a calculated field on the form and displayed it in the browse:

datenextlast = r2v.rdate

This should display the date of the other event just as a test. The only date that came up was the first record in the table for every row. This has pushed my knowelege to way past comfortable. What do you think?

[Ray in Capetown]

Re: Advice - Working with an array

Great so far John, you grasp the principle of reading all fields in answer to the thread topic .
What you seem to have now is a problem locating the correct record. Try work it out in the Interactive Window first.
Else Post a question on another topic if you want to help on that.
Also open another instance of the reservations table than the one the browse has open - ie not with table.get("res_detail") but as in the code segment,

[johngtatp]

Re: Advice - Working with an array

Ray,

Thanks, I think I have the basics working and I have to tweak it a bit. Thanks for your help, never would of thought of such a thing. The question is if this will help my speed, that I will see. Thanks again!