1

I have successfully created a custom dialog box which uses an array to read in a list of developer names from a table, and populates the selected record based on the row number pulled in from the developer table.

Table is Developer ID Developer Name 1 A developer 2 B developer 3 C developer 4 D Developer 5 AAA Developer 6 C2 developer

I have also had success in then populating the selected value from the popup menu back to the table. At present the developer list contains about 400 names. As new names are found, they need to be added to the table. In above example- ID 1 to 4 were added initially then 5 and 6 later and they aren't sorted alphabetically.

My question is does anyone know if there any way that I can I get the table from the array in alphabetical order without upsetting the row numbers from the developer table? If I add say another 50 developers to the bottom of the table then it becomes hard to find them as they are not sorted alphabetically - whilst the first 400 I put in are.

Given that the popup menu reads in the row number and assigns this to the value in the popup menu then writes it based on the value selected in the popup, I am unsure whether this can be done.

iRows1 = TableInfo(developer, TAB_INFO_NROWS)

'redim developername array based on number of rows in table
ReDim sDeveloperName1(iRows1)

For iLoop1 = 1 to iRows1
    Fetch rec iLoop1 from developer
    sDeveloperName1(iLoop1) = developer.developername
Next


'Developer details groupbox
control groupbox
title "Developer Details"
Position 5,206 width 130 height 97
control statictext position 10,216 title "Primary developer"
control popupmenu position 10,228 width 120 ID 20
Title from Variable sDeveloperName1 into iDeveloperID1out


Sub ReadIn_PopUpMenu_Items
    'converts table value into the popupmenu item for primary developer listed
     dim developerID1temp as integer
     developerID1temp=selection.developerID1
     if developerID1temp=0 then
         alter control 20 value 1
    Else
        alter control 20 value developerID1temp
    End if
End

'if 1st value in popup menu is selected then developer is unknown so write the 'value back to the selected record as 0
If iDeveloperID1out=1 then
    Update Selection set col20=0
else
    Update Selection set col20=iDeveloperID1out
End If
0

A very simple solution to your request would be to use a sorted table for reading and writing back the data/developer names.

Currently you seem to be using the base table called Developer. You would basically just add a SQL Select that sorts the table and then use the sorted query in stead of the base table.

Select * From DEVELOPER
   Order By DEVELOPERNAME
   Into __DEVELOPER__SORTED NoSelect

Now you reading of the developer names would look like this:

iRows1 = TableInfo(__DEVELOPER__SORTED, TAB_INFO_NROWS)

'redim developername array based on number of rows in table
ReDim sDeveloperName1(iRows1)

For iLoop1 = 1 to iRows1
    Fetch rec iLoop1 from __DEVELOPER__SORTED
    sDeveloperName1(iLoop1) = __DEVELOPER__SORTED.DEVELOPERNAME
Next

It's important that you also use the sorted table when writing data back as the ROWID refers to the sorted query not the base table.

And I would always recommend using a Do Until EOT() loop when looping thru a table - never a For loop:

iRows1 = TableInfo(__DEVELOPER__SORTED, TAB_INFO_NROWS)

'redim developername array based on number of rows in table
ReDim sDeveloperName1(iRows1)

Fetch First From __DEVELOPER__SORTED
Do Until EOT(__DEVELOPER__SORTED)
    iLoop1 = __DEVELOPER__SORTED.ROWID
    sDeveloperName1(iLoop1) = __DEVELOPER__SORTED.DEVELOPERNAME
    Fetch Next From __DEVELOPER__SORTED
Loop

Also looking a bit more at your code it seems that you update another table with the ROWID from the DEVELOPER that. If that's true you are definitely going down the wrong lane. The ROWID of a table is very dynamic; if you pack the table the ROWID might change.

I would recommend that you add a more static ID column to your DEVELOPER table and use that in stead. You could change the reading to this:

iRows1 = TableInfo(__DEVELOPER__SORTED, TAB_INFO_NROWS)

'redim developername array based on number of rows in table
ReDim sDeveloperName1(iRows1)
ReDim nDeveloperID1(iRows1)

Fetch First From __DEVELOPER__SORTED
Do Until EOT(__DEVELOPER__SORTED)
    iLoop1 = __DEVELOPER__SORTED.ROWID
    sDeveloperName1(iLoop1) = __DEVELOPER__SORTED.DEVELOPERNAME
    nDeveloperID1(iLoop1) = __DEVELOPER__SORTED.DEVELOPERID
    Fetch Next From __DEVELOPER__SORTED
Loop

And you can change the setting of the developer and the update to this:

Sub ReadIn_PopUpMenu_Items
    'converts table value into the popupmenu item for primary developer listed
dim developerID1temp, i as integer
    developerID1temp=selection.developerID1
    if developerID1temp=0 then
        alter control 20 value 1
    Else
        For i = 1 To Ubound(nDeveloperID1)
            If developerID1temp = nDeveloperID1(i) Then
                alter control 20 value i
            End if
        Next
    End if
End

'if 1st value in popup menu is selected then developer is unknown so write the 'value back to the selected record as 0
If iDeveloperID1out=1 then
    Update Selection set col20=0
else
    Update Selection set col20=nDeveloperID1(iDeveloperID1out)
End If

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.