Sync a ListBox contents with a Source Text data ?

Emile_Schwarz · October 13, 2015, 9:48am

Hi,

I have a ListBox filled with data, notably a Date Start (as SQLDate) and a Source Text File who also holds that Date Start (as SQLDate).

The ListBox contents have been generated by the software while the Source Text file was man made.

I want to merge both data and I use the minimum commondata: the Date Start value.

This would works (what I already have) fine using Sunday Dates only (probably, not sure), the date flow from Monday to Saturday (then skip SUnday) in both ListBox and Source Text.

What Ive done is:

In a loop: (While Not t.EOF / Wend)
I read the ListBox (the Date Start column)
I read one line from the Source Text file

I compare the Date Start (from the ListBox) to the Date Start (from the Source Text file).
If not found, I increment this sub-loop and check once more
When found, I add the sub-loop index to the main index for the newt Date Start

For the first date, it works fine, so I assume the trouble i not there.

After the first run, I realized why it does not worked: I had to search in the ListBox the next Date Start, several Rows later (since the next one cannot be in the Source TEXT file, but I have to determine it by code: the lenth [number of Mon-Sat is unknow and can vary]).

So, I added a simple For / Next loop to search the next occurence (next Date Start in the ListBox that matches the one in Source Text file).

I wasted hours yesterday to nowhere and I get no new idea at woke up time today.

Can you:

tell me if my ideas flow description above can be correct (or f you see an error),
give me advice to a better approach to resolve the synchronisation process ?

In the mean time, I will try to draw (symbolic process with a pen on a sheet of paper) the process and try to discover what was running wrong.

Thanks.

PS: right now (just like yesterday), the design (expressed above) seems to be OK: but Xojo says: “Niet !”

I forget to say that both data hold mostly different data, but that complements each one to the other and so it is a merge of the two data (better be done by the computer than by the man).
In the data writing process, I modify a Cell only if it is empty. I commented that part out for the testings - writing only in a know empty column a report value (the general Loop Indice as a debug process).

Tim_Hare · October 13, 2015, 8:11pm

This is a fairly common algorithm and requires you to maintain 2 indexes - one in each data set. You initialize each index to the first record in each data set and then loop until you have exhausted both sets. At each iteration you compare the key (date) of the current records and

if the date of the listbox is less than the date of the file record, add the listbox record to the result and increment just the index for the listbox.
if the date of the listbox is greater than the date of the file record, add the file record to the result and increment just the index of the file data set.
if the dates are equal, merge the 2 records, add that to the result and increment both indexes.

There is a special case to consider if you have reached the end of one or the other data set, so test that condition first:

0.1) if you’re at the end of the file data set, add the listbox record to the result and increment the listbox index.

0.2) if you’re at the end of the listbox, add the file record to the result and increment the file index.

1-3 above.

The key is that you process one and only one record at each iteration of the loop. Don’t try to get fancy and look ahead.

Emile_Schwarz · October 14, 2015, 7:33am

Hi Tim,

Thank you for your concise explanation: I found stuff to think at, notably in point 3.

I have tor ead more carefully points 1 and 2.

In the mean time, I found my error. In the minor Loop, the way I think the program run flow exits was not what I really think, so:

in lline 1 (Row 0), the program exits the minor loop (your dot 2 above) using the way I think all entries will exits,
all other lines (Row 0 to end of the Source Text contents) exits differently: I do not updated the main loop index !

After the end of the minor loop, I added the minor loop index to the main loop index and all went OK !

Amazing how simple things may be to solve a trouble ;-

Alternate question: why do I do not think that my Source Text file is always smaller than the generated ListBox contents ? Strange !