Looking for more efficient way

#1 · October 21, 2021, 5:45 pm

I have an app part of which is to generate draw numbers for approximately 400 members.

My script seen to take a while. Looking for a more efficient routine.

MessageBox "Draw Numbers Change" "You are about to generate New Member Draw Numbers.|Proceed ?" "Ok|Cancel" "[NewDrawNumbers]"
If "[NewDrawNumbers]" "=" "1"
CustomWindow "Draw Number Building" "432" "316" "WorkingMsg" "DialogBox"
dbpShowAll "AddrBook" "Contacts"
. clear existing numbers
. test sql update
dbpExecSQL "AddrBook" "UPDATE Contacts SET DrawNumber = 0" ""
dbpRefresh "AddrBook" "Contacts"
.AlertBox "Sql " "SQL done"
.dbpExecSQL "AddrBook" "" ""
dbpFirst "AddrBook" "Contacts"
. add new numbers
..dbpExecSQL "AddrBook" "UPDATE Contacts SET DrawNumber = ROW_NUMBER() Where ActiveStatus = True " ""
..dbpRefresh "AddrBook" "Contacts"
dbpQuery "AddrBook" "Contacts" "ActiveStatus = True"
dbpSort "AddrBook" "Contacts" "LastName=ASC;FirstName=ASC"
dbpFirst "AddrBook" "Contacts"
Loop "1" "[AddrBook.Contacts.$RecCount]" "[loopnum]"
SetVar "[AddrBook.Contacts.DrawNumber]" "[loopnum]"
dbpNext "AddrBook" "Contacts"
EndLoop
CloseCustomWindow "WorkingMsg"
AlertBox "Draws Numbers" "New Draw Numbers have been assigned to Active Members."

#2 · October 21, 2021, 10:37 pm

@cssystems

Using dbpExecSQL will definitely speed things up ... take a look at the A) Simple SQL ROW_NUMBER() example here ... https://www.sqltutorial.org/sql-window-functions/sql-row_number/ ... and add a WHERE clause to limit the sequencing to records containing ActiveStatus = True

SELECT
ROW_NUMBER() OVER (
ORDER BY salary
) row_num,
first_name,
last_name,
salary
FROM
employees;

If you need further assistance, please post a sample/test database (mdb) and a minimalist pub file.

#3 · October 24, 2021, 6:45 pm

Using Gaev's suggestions,

I am working on smaller app using the database in question.

So far with "missing operator" error

dbpExecSQL "DBTest" "SELECT ROW_NUMBER() OVER ( ORDER BY LastName,FirstName) row_num ,FirstName, LastName FROM Contacts;" ""

Also, how do I get the row_num assigned to the DrawNumber field I have in the database?

Will supply the app if necessary.

#4 · October 24, 2021, 9:07 pm

@cssystems

Will supply the app if necessary.

No need for the app; just the .MDB file ... about 20 records will do; can change first and last names to protect privacy of real people.

#5 · October 24, 2021, 9:43 pm

@cssystems

If you have 400 records of which (say) 380 are active; if you only need to identify just the one record with a matching "Draw Number", you might consider another approach.

1) Use dbpQuery command to filter out the inactive records.

2) Use dbpSort to sort them in the desired order

3) Do your thing (subroutine) to determine the [DrawNumber]

4) dbpGotoRecord "database id" "table" "[DrawNumber]"

Is this what your requirement is ?

#6 · October 25, 2021, 2:19 am

I want to start at the top of the Membership List in Alphabetical order by LastName, First Name

and assign a draw number to each 1,2,3,... to end of list.

This is for a Loonie/Toonie draw which was stopped because of Covid-19 at our Legion Branch.

I am assigning new draw numbers to each active member.

I used Loop to do the job, but perhaps While statement is better.

You got me thinking about SQL to do the job.

Looking for more efficient way - Forum

Looking for more efficient way