How do I refresh dialog after updating an array?

[Al Buchholz]

My Hobby is to travel around visiting airfields and museums at home and abroad, trying to see as many different aircraft as I can. (Look on it as a hunter bringing back trophies to mount on his wall, except that I only record the aircraft identity in a notebook).

I have a table called LOGGING of over 500,000+ with details on each individual aircraft and airfield including the date, place and Current Reg that I first saw any individual aircraft.

My hand written log usually contains only the registration, date and location seen.

When I get home I want to do the following:

1. compare the list of REGs against the table LOGGING to create a list of those present in the table and a separate list to show those that are not. (they can be missing for several reasons, and there can be duplicate REGs as well).
2. Update the date, where and seen as Reg from my log to the main table for those not seen before (ie date, where and seen as are blank).
3. Add the missing to the table with all the necessary data in its relevant fields (ie more than just date, where and seen such as aircraft type)
4. Append the full list to a table called Current_Logging which is a complete diary of everything I have seen over the year
5. Hope that helps.

Sounds like sets and append or posting operations should be doing a lot of this work.

I've watched you work on this for years and always been interested in your hobby.

I'm an inactive pilot that appreciates aircraft.

They are beautiful creatures.

[Mike Wilson]

Hi Graham,
You wrote:

1. compare the list of REGs against the table LOGGING to create a list of those present in the table and a separate list to show those that are not. (they can be missing for several reasons, and there can be duplicate REGs as well).
2. Update the date, where and seen as Reg from my log to the main table for those not seen before (ie date, where and seen as are blank).
3. Add the missing to the table with all the necessary data in its relevant fields (ie more than just date, where and seen such as aircraft type)
4. Append the full list to a table called Current_Logging which is a complete diary of everything I have seen over the year
5. Hope that helps.

I'm trying to understand the bolded.
1. I believe #1 list the list from your notebook from your recent excursion? Present means reg in the logging table with some semblance of accompanying data and those that are not is the result of the search into the logging table for the reg not having a matching result and therefore destine to become a new record.
2. Not understandable is how you can update something you have not seen before.
3. Not understandable is what the difference is between missing and not seen before and those that are not present.
4. Is this supposed to be over the years

I am also not quite certain whether or not you are trying to maintain the records of prior sightings of an airplane (reg) so you have a historical record of the sightings with when and where. You use the term update so it makes me believe you are only wishing to maintain the current data for the airplane (reg) and not saving prior sighting data for historical purposes.

I'm working up an example of what I believe you are wishing, but I need clarity on the above to get closer to your target.

[Graham Wickens]

I'm trying to understand the bolded.(finger trouble)

1. I believe #1 list the list from your notebook from your recent excursion? Present means reg in the logging table with some semblance of accompanying data and those that are not is the result of the search into the logging table for the reg not having a matching result and therefore destine to become a new record. Correct

2. Not understandable is how you can update something you have not seen before.

the "LOGGING" table contains all the individual aircraft information I am interested in, some I have seen, some I have not. When I say Update, I mean I add the Date,when and where to the relevant Reg in my table after having seen it.


3. Not understandable is what the difference is between missing and not seen before and those that are not present.

a) Missing means aircraft with that REG are not in table, usually new builds
b) not seen before means date,when and where fields are blank in relevant record
c) those that are not present means those that have been re-registered and my table does not yet reflect this even though the aircraft is in the table under another reg, so it has to be update with new reg during logging

the main reason for splitting the missing is so I can sort out all the neccessary updating outside of the script.

4. Is this supposed to be over the years

I am also not quite certain whether or not you are trying to maintain the records of prior sightings of an airplane (reg) so you have a historical record of the sightings with when and where. You use the term update so it makes me believe you are only wishing to maintain the current data for the airplane (reg) and not saving prior sighting data for historical purposes.

I am doing both
a) only maintaining first sightings in my LOGGING Table.
b) appending complete trip log to another table (Current_logging) for historical purposes.

I'm working up an example of what I believe you are wishing, but I need clarity on the above to get closer to your target.

You are vey kind, thank you

[Mike Wilson]

OK Graham,
What and how you are explaining this is confounding and not understandable of what you have in place and what you are doing. I'll keep trying if you can handle my drilling,

1. GRAHAM:
the "LOGGING" table contains all the individual aircraft information I am interested in, some I have seen, some I have not. When I say Update, I mean I add
the Date, when and where to the relevant Reg in my table after having seen it.

1. MIKE:
This implies your LOGGING table consists of a record with a field for the aircraft type (ex: Schleicher ASK21) and a REG (ex: G-CHYT) and then you see
this aircraft FOR THE FIRST TIME you complete the record by adding the date seen and the location where the sighting occurred from the notebook entry.
This is corroborated with your statement:

2. GRAHAM:
My hand written log usually contains only the registration, date and location seen.

2. MIKE:
This implies that the REG defines the aircraft and is the key field in your LOGGING table. HOWEVER:

3. GRAHAM:
a) Missing means aircraft with that REG are not in table, usually new builds

3. MIKE:
This does not say that the REG is not in the table but the combination REG-Aircraft is not in the table which corroborates #1 above.

4. GRAHAM:
c) those that are not present means those that have been re-registered and my table does not yet reflect this even though the aircraft is in the table
under another reg, so it has to be update with new reg during logging.

4. MIKE:
This is where it comes to focus that what you are saying doesn't form cohesive understandable information. Since the REG is the key to the aircraft,
if the REG is not in the table, then it is missing, (#3 above) and there is nothing to go on. So how could you possibly make any determination about any aircraft
if the key to that aircraft, the REG, doesn't exist. That said, #4 seems to be confabulation.

[Graham Wickens]

OK Graham,
What and how you are explaining this is confounding and not understandable of what you have in place and what you are doing. I'll keep trying if you can handle my drilling,

1. GRAHAM:
the "LOGGING" table contains all the individual aircraft information I am interested in, some I have seen, some I have not. When I say Update, I mean I add
the Date, when and where to the relevant Reg in my table after having seen it.

1. MIKE:
This implies your LOGGING table consists of a record with a field for the aircraft type (ex: Schleicher ASK21) and a REG (ex: G-CHYT) and then you see
this aircraft FOR THE FIRST TIME you complete the record by adding the date seen and the location where the sighting occurred from the notebook entry.
This is corroborated with your statement:
Correct

2. GRAHAM:
My hand written log usually contains only the registration, date and location seen.

2. MIKE:
This implies that the REG defines the aircraft and is the key field in your LOGGING table. HOWEVER:

I have a field called "UNIQUE" in the table which is the actual record KEY field. But I dont know this until I search on the REG field, but yes the REG defines the key field in this instance.
there can be multiple instances of REG each with its own Unique.

3. GRAHAM:

a) Missing means aircraft with that REG are not in table, usually new builds, but the aircraft could be there under another REG (My table is out-of-date)

3. MIKE:
This does not say that the REG is not in the table but the combination REG-Aircraft is not in the table which corroborates #1 above.

Correct

4. GRAHAM:
c) those that are not present means those that have been re-registered and my table does not yet reflect this even though the aircraft is in the table
under another reg, so it has to be update with new reg during logging.

4. MIKE:
This is where it comes to focus that what you are saying doesn't form cohesive understandable information. Since the REG is the key to the aircraft,
if the REG is not in the table, then it is missing, (#3 above) and there is nothing to go on. So how could you possibly make any determination about any aircraft
if the key to that aircraft, the REG, doesn't exist. That said, #4 seems to be confabulation.

Ha! had to look that word up :)

yes indeed, ignore 4)

so MIssing contains all entries that do not match a REG in the LOGGING table, which gives me a list to process outside of the logging process.

---

[Graham Wickens]

Finally got rid of my headache so can think a bit more clearly for a while, been totally confusing you and myself (Life long habit!).

you can see why I dont make a very good programmer, I'm ok with tables,forms, browses and reports it is the DESIGN that I have no ideas on, I have just blundered along evolving my database over about 20 years or more.

start again:

my written log is transposed to a txt file will contain REG,WHEN,WHERE then into an array (WRITTEN_LOG)

my LOGGING Table has fields (amongst others) REG, AS, WHEN, WHERE that I am interested in here.

"AS" contains the REG I first saw the individual aircraft carrying, when and where are first time seen when and seen where.

for matching entries in written log (WRITTEN_LOG->REG = LOGGING->REG) I want to add WRITTEN_LOG->REG to LOGGING->AS and the corresponding Written_Log->WHEN and Written_Log-> WHERE only if LOGGING->AS is blank

Duplicate LOGGING->Reg matches need displaying so I can manually choose correct one if it is listed and then add as,when and where to that entry.

If no matches found, I then need to create a list of MISSING, so that I then know what I have to look for and update/append to the LOGGING table later.(Outside the remit of this script).

Finally I append the complete Written_Log array to a historical table "Current_Logging" (including the aircraft_type details plus logging details from table LOGGING).

hope that is clearer ?

[Ted Giles]

graham02.vsd.png
Is this anything like the process, Graham?

You may have Visio or Paint to do a similar diagram.

Else, http://freemind.sourceforge.net/wiki....php/Main_Page is a good way of designing processes using Freemind. Excellent free software for decision trees and such.

[Graham Wickens]

That looks correct.

will have a look at Freewill, might help me design better in the future

[Ted Giles]

I doubt you wish to be deeply involved in designing stuff for other people to build, however having been on both ends of the development cycle, I find it sometimes pays to take a step back and simply write up what I am trying to achieve.
Example.
I am working with a USA company developing an unusual application.
When I viewed their original concept, it was overwhelming. Drawing out a few boxes simplified it enormously and we are getting there more quickly and less expense is required.
Sometimes we get bogged down in the detail, or along a path we may regret starting out on.

Anyway, Mike will know what to do now, I suspect.

[Mike Wilson]

Hi Graham,
OK, I'm not sure I have this all straight in my head but here is an example with mock data with a structure in the direction that I believe I understand your setup to be. This exemplifies a dialog with the functionality to use focused, dynamic searches and record add-edit-save functionality and that kind of stuff. Hope there is something from this that might help. I'd be more than happy to help further.

[Graham Wickens]

Thank you very Much Mike,
I am away til Thursday (chasing airplanes), so will I will slowly read and digest when I return.

[Graham Wickens]

Hi Mike,

had a look at your code, still trying to decifer some of it,.

looking at the dialog layout got me thinking.

I wrote a script to import my written log into a table.

then created a Set with this written log table as the Parent and my LOGGING table as the child.

I can now do what you have suggested on a form with various buttons for all the sub tasks.

seems to work just fine (using about a third of the code I was using before).

thx

[Mike Wilson]

Great Graham! Glad it worked out for you.

[madtowng]

I realize I'm bypassing a lot of the stuff that I can't follow, but the query restriction just seems to scream for the efficiency of SQL.
I have taken the liberty of creating a mySQL table with what I presume are aircraft registrations (at least that's what I called the data
in the sample filter code). Code to create the table and populate it with that data can be found at https://paiza.io/projects/XbXLQXxyYG...language=mysql
to make things easier.
Once that is in place, the code below makes things so much easier.
Maintaining sql tables is relatively simple, as is populating a dbf table with the results of a sql query.
This code has been tested and does work.
As an after thought, you can get away with using sql_lookup with not opening the connection.
(This example does use mySQL or mariaDB. You might have to make adjustments if you use a different backend db)

dim haveIseenIt as n
dim cn as sql::connection
dim args as sql::arguments
cn.open("::name::regs")
haveIseenIt = -1
'I am going to call the lookup value searchValue. Replace it with your variable
searchValue = "D-ABFE"
args.Set("checkThis",searchValue)
haveIseenIt = sql_lookup(cn,"acregistrations","reg = :checkThis","count(*)",args)

' Values returned should be 0 (haven't seen it) or 1 (you have seen it). Anything else suggests a problem of some kind