[RESOLVED] Best way to update dynamic records

[Chrissy]

I have a Budget program written in VB6 and it writes to an Access database using ADO.

A little background... It is for a commercial real estate firm. The budget uses projected income and expenses from each current tenant and the probability of a vacant space being leased within the lease term etc. There are multiple complicated calculations in relation to forecasting operating expenses and real estate tax escalations.

The user clicks a drop down and selects one of the accounts with the complex calculation. For each account, it takes the revenue of each tenant and calculates out each month. The is one row of data for each tenant and spec tenant. Depending on the tenant data when updated, the actual tenants could be different causing some records to need to be deleted instead of updated. I have the program first deleting all the records in the table and then adding it as new once calculated. I am doing it this way because I can't think of an easier way to update the table that would allow for the dynamics of the tenants.

For example, the data could look like this...

Old Data New Data
Tenant 1 Tenant 1
Tenant 3 Tenant 5
Tenant 5 Tenant 8
Tenant 8 Tenant 9

Therefore, when updating the new data, Tenant 3 should be deleted and Tenant 9 should be added.

I hope this makes sense....

Any thoughts are welcome.

Thanks!

Chrissy

[sapator]

First let me.....Death to Access!Don't use this crap!
OK,now.
If your tenant(don't have a clue what that is) is based on some criteria,for example one tenant for a house leased will have the same, let's say, Revenue number but the number calculated for that tenant will change then you can easily have a primary id for each tenant and update the tenant based on the id key. You therefore just update and not delete each tenant.It does not seem logical to delete all the values each time your insert data....So did i say death to access, etc,etc...I did.If you make this to a multiuser environment program then you will "love" using access too, as most of us do. Change the DB if you can (you are deleting everything each time anyhow so no stable data has been established yet).

[FunkyDexter]

Your set up is slightly odd but if I'm understanding you correctly then a delete and recreate is perfectly acceptable unless you want to keep some audit of the changes. What's odd in you situation is that you seem to be infering the change from the new state rather than calculating the new state from the change.

I.e. In the "normal" course of events for a database update you know what you're data is going to start out as:-
Tenant 1
Tenant 3
Tenant 5
Tenant 8

...and you know the changes you want to apply:-
Remove Tenant 3 and Create Tenant 9

so you make those changes in the form of inserts and deletes to produce your updated state:-
Tenant 1
Tenant 5
Tenant 8
Tenant 9

But it sounds like your system doesn't really work that way. Instead you know what your data is going to start out as and you know what you want it to end up as but you don't start out knowing what the changes are. If you don't need any kind of audit trail then just deleting the contents of the table and recreating the new state from scratch is fine because you don't care what changed or what the old state was.

If you do want an audit trail then things are going to be a bit more complcated. You're going to need to compare the old state to the new state and infer the changes. You're then going to need to somehow store those changes and apply them.

[Chrissy]

Your set up is slightly odd but if I'm understanding you correctly then a delete and recreate is perfectly acceptable unless you want to keep some audit of the changes. What's odd in you situation is that you seem to be infering the change from the new state rather than calculating the new state from the change.

I.e. In the "normal" course of events for a database update you know what you're data is going to start out as:-
Tenant 1
Tenant 3
Tenant 5
Tenant 8

...and you know the changes you want to apply:-
Remove Tenant 3 and Create Tenant 9

so you make those changes in the form of inserts and deletes to produce your updated state:-
Tenant 1
Tenant 5
Tenant 8
Tenant 9

But it sounds like your system doesn't really work that way. Instead you know what your data is going to start out as and you know what you want it to end up as but you don't start out knowing what the changes are. If you don't need any kind of audit trail then just deleting the contents of the table and recreating the new state from scratch is fine because you don't care what changed or what the old state was.

If you do want an audit trail then things are going to be a bit more complcated. You're going to need to compare the old state to the new state and infer the changes. You're then going to need to somehow store those changes and apply them.

Maybe I need to post this in the visual basic forum instead.... I am thinking from a programmatic point of view. The program does not know what data is new/old. Therefore, if I query the database for a tenant and update the data that way (update existing and add new ones) it does not address the tenant that needs to be deleted.

[Chrissy]

I first posted in the DB section with no luck...

I have a Budget program written in VB6 and it writes to an Access database using ADO.

A little background... It is for a commercial real estate firm. The budget uses projected income and expenses from each current tenant and the probability of a vacant space being leased within the lease term etc. There are multiple complicated calculations in relation to forecasting operating expenses and real estate tax escalations.

The user clicks a drop down and selects one of the accounts with the complex calculation. For each account, it takes the revenue of each tenant and calculates out each month. The is one row of data for each tenant and spec tenant. Depending on the tenant data when updated, the actual tenants could be different causing some records to need to be deleted instead of updated. I have the program first deleting all the records in the table and then adding it as new once calculated. I am doing it this way because I can't think of an easier way to update the table that would allow for the dynamics of the tenants.

For example, the data could look like this...

Old Data ................... New Data
Tenant 1 ................... Tenant 1
Tenant 3 ................... Tenant 5
Tenant 5 ................... Tenant 8
Tenant 8 ................... Tenant 9

Therefore, when updating the new data, Tenant 3 should be deleted and Tenant 9 should be added.

First I was querying the database for each new tenant and updating existing and then adding new, but it did not address the ones that need to be deleted.

I hope this makes sense....

Any thoughts are welcome.

Thanks!

Chrissy

[dilettante]

Do you have a database or just a single table?

Without normalization which means multiple linked tables you can't have relations, and the sort of updates you describe can be a pain.

It sounds as if you have a list of "Accounts" and a list of "Tenants." Individual Tenants are related to one and only one Account with the possibility that a given Tenant might be "free" and not yet related to any Account. I can't guess what a "spec tenant" might be though.

By making these separate tables and connecting records via relations a lot of what you describe becomes pretty easy.


For example you might provide a list for picking from or a query by Name or AccountNumber used to select an Account. Once you have this you can just query Tenants by the foreign key (usually an autonumber ID field) to get the list of Tenants for that Account.

Deleting Tenants from that Account is easy then: just let the user pick those to delete either one by one or via checkboxes or something. You could even have both a delete function and an orphan function that nulls the foreign key field.

Just as easy to "move" a Tenant to another Account or add a "free" Tenant to an Account, merely by updating the foreign key.


Pretty basic stuff. Unless I have misinterpreted what you want to do.

[szlamany]

Ok - you have two threads going on this - so please will some moderator fix that up!

Otherwise - you need to show tables and what you are using for code to update those tables...

[dilettante]

Probably best over in the database forum.

[Chrissy]

DB Relationships.pdfExample.pdf

dilettante....

It is a database, not a table. I attached the relationships. It is not possible for the tenant to pick and choose which tenants to delete... kind of defeats the process of automation.

One Mkt_assump has multiple accounts and one account has multiple detail. The detail for certain accounts is details rental data on each tenant in one building. The detail table is the one in question. I was trying to simplify what I am trying to accomplish.

I also attached an example... it would be the same logic of updating a database with a series of check boxes in a program.

I believe this should stay in the VB section because it is more of a coding question.

[Chrissy]

You asked for the code... here is the least complicated one... It is pretty involved.

Public Sub LoadRent(ByVal xType As String, ByVal Exp As Boolean, ByVal PropNum As String, ByVal xYear As String, ByVal AcctNum As String)

Months(1) = "jan"
Months(2) = "feb"
Months(3) = "mar"
Months(4) = "apr"
Months(5) = "may"
Months(6) = "jun"
Months(7) = "jul"
Months(8) = "aug"
Months(9) = "sep"
Months(10) = "oct"
Months(11) = "nov"
Months(12) = "dec"

Call clsDB.rsInit(rsRec)

rsRec.Source = ("SELECT l.* FROM lease l " _
+ " WHERE year = '" + xYear + "' AND l.prop_num = '" + Format(PropNum, "00000") + "' AND EXISTS (SELECT * FROM rent r " _
+ " WHERE ('" + Format(xYear) + "' BETWEEN datepart('YYYY',start_date)" _
+ " AND datepart('YYYY',end_date) OR end_date is null) " _
+ " AND type = '" + xType + "' AND l.tcode=r.tcode and l.add_tenant = " _
+ " r.add_tenant and l.prop_num = r.prop_num AND l.year = '" + xYear + "') Order by l.Tcode")

rsRec.Open

Call clsDB.rsInit(rsNew)

'Here is where I delete all the current records.
rsNew.Source = ("SELECT * FROM detail WHERE year = '" _
    + xYear + "' AND prop_num = '" + Format(Trim(PropNum), "00000") + "' AND " _
    + "acct_num = '" + AcctNum + "'")
    
rsNew.Open

Do While Not rsNew.EOF
    rsNew.Delete
    rsNew.MoveNext
Loop

xRow = 1
xCount = 1

RentNum = 0
Do While Not rsRec.EOF

    rsNew.AddNew
    rsNew!Year = xYear
    rsNew!prop_num = PropNum
    rsNew!acct_num = AcctNum
    rsNew!detail_num = Info.CreateDetailNum(xYear, PropNum)
    rsNew!Desc = Trim(rsRec!TName)

    For i% = 1 To 12
        
        Call clsDB.rsInit(rsRent)
        
        rsRent.Source = ("SELECT * FROM rent WHERE prop_num = '" _
        + Format(PropNum, "00000") + "' AND year = '" + xYear + "' AND Tcode ='" + rsRec!Tcode + "' AND " _
        + " (#" + Format(i, "00") + "/" + Format(xYear) + "# BETWEEN " _
        + " start_date and end_date OR #" + Format(i, "00") + "/" + Format(xYear) + "# >= " _
        + " start_date AND end_date is null) " _
        + " AND type = '" + xType + "' Order by Tcode")

        rsRent.Open
        RentNum = 0
        Do While Not rsRent.EOF

            If rsRent.RecordCount = 0 Then
                rsNew.Fields.Item(Months(i)) = 0
            Else
                Total = Total + Round(rsRent!amount, 0)
                rsNew.Fields.Item(Months(i)) = Round(rsRent!amount, 0)
                MonthTotal(i) = MonthTotal(i) + Round(rsRent!amount, 0)
                
            End If

            rsRent.MoveNext
        Loop
        rsRent.Close
        
    Next i%
    
    rsNew.Update
    xCount = xCount + 1
    
    MonthTotal(13) = MonthTotal(13) + Total

    Total = 0
    
    xRow = xRow + 1
    rsRec.MoveNext
    
Loop

rsRec.Close

Call clsDB.rsInit(rsSpec)
    
rsSpec.Source = ("SELECT * FROM lease WHERE prop_num = '" _
    + Format(PropNum, "00000") + "' AND add_tenant IN ('R','S') AND year = '" + xYear + "'")

rsSpec.Open


Do While Not rsSpec.EOF

    Found = False
    rsNew.AddNew
    rsNew!Year = xYear
    rsNew!prop_num = PropNum
    rsNew!acct_num = AcctNum
    rsNew!detail_num = Info.CreateDetailNum(xYear, PropNum)
    
    If Day(rsSpec!commence_date) > 1 Then
        CommDate = Format(Format(Month(DateAdd("m", 1, rsSpec!commence_date)), "00") + "/01/" + Format(Year(DateAdd("m", 1, rsSpec!commence_date))), "MM/DD/YYYY")
    Else
        CommDate = Format(rsSpec!commence_date, "MM/DD/YYYY")
    End If

    If IsNull(rsSpec!expiration_date) Then
        ExpDate = DateAdd("yyyy", Val(Left(rsSpec!lease_term, 2)), rsSpec!commence_date)
        ExpDate = DateAdd("m", Val(Right(rsSpec!lease_term, 2)), ExpDate)
        ExpDate = DateAdd("d", -1, ExpDate)
    Else
        ExpDate = rsSpec!expiration_date
    End If
    For i% = 1 To 12
    
    
    If DateValue(ExpDate) >= DateValue(Format(i, "00") + "/" + Format(xYear)) And ((Month(rsSpec!commence_date) <= i And Format(Year(rsSpec!commence_date)) = xYear) Or Format(Year(rsSpec!commence_date)) <= xYear) Then

        If Not IsNull(rsSpec!free_rent) And Val(Format(rsSpec!free_rent, "General Number")) <> 0 Then
        
            If xType = "free-rnt" Then
                If DateValue(Format(DateAdd("m", rsSpec!free_rent, DateValue(CommDate)), "MM/YYYY")) > DateValue(Format(Format(i, "00") + "/" + Format(xYear), "MM/YYYY")) And DateValue(Format(Format(i, "00") + "/" + xYear, "MM/YYYY")) >= DateValue(CommDate) And Right(CommDate, 4) <= xYear Then
                    
                    If rsSpec!add_tenant = "R" Then
                        rsNew!Desc = Trim(rsSpec!TName) + " (Renewal)"
                    Else
                        rsNew!Desc = Trim(rsSpec!TName) + " (" + Trim(rsSpec!Tcode) + " - Suite " + Trim(rsSpec!Suite) + ")"
                    End If
                        
                    Total = Total + Round(FormatNumber(-(rsSpec!rental_psf * rsSpec!SQFT) / 12, 2), 0)
                    rsNew.Fields.Item(Months(i)) = FormatNumber(Round(-(rsSpec!rental_psf * rsSpec!SQFT) / 12, 0), 0)
                    MonthTotal(i) = MonthTotal(i) + Round(FormatNumber(-(rsSpec!rental_psf * rsSpec!SQFT) / 12, 2), 0)
                    Found = True
                End If
            Else
                
                If (Right(CommDate, 4) = xYear And Val(Left(CommDate, 2)) <= i) Or Right(CommDate, 4) < xYear Then
                    If rsSpec!add_tenant = "R" Then
                        rsNew!Desc = Trim(rsSpec!TName) + " (Renewal)"
                    Else
                        rsNew!Desc = rsSpec!TName + " (" + rsSpec!Tcode + ")"
                    End If
                    xTempMonth = 0
                    If (Year(DateAdd("m", 12, DateValue(CommDate))) = Val(xYear) And Month(DateAdd("m", 12, DateValue(CommDate))) <= i) Or Year(DateAdd("m", 12, DateValue(CommDate))) < xYear Then
                        'add 5/23/2012 to accomodate multiple ways of entering step.
                        If Not IsNull(rsSpec!annual_rent_step) Then
                            rsNew.Fields.Item(Months(i)) = Round(FormatNumber(((rsSpec!rental_psf + (rsSpec!rental_psf * (rsSpec!annual_rent_step / 100))) * rsSpec!SQFT) / 12, 2), 0)
                            xTempMonth = Round(FormatNumber(((rsSpec!rental_psf + (rsSpec!rental_psf * (rsSpec!annual_rent_step / 100))) * rsSpec!SQFT) / 12, 2), 0)
                            MonthTotal(i) = MonthTotal(i) + Round(FormatNumber(((rsSpec!rental_psf + (rsSpec!rental_psf * (rsSpec!annual_rent_step / 100))) * rsSpec!SQFT) / 12, 2), 0)
                            Total = Total + Round(FormatNumber(((rsSpec!rental_psf + (rsSpec!rental_psf * (rsSpec!annual_rent_step / 100))) * rsSpec!SQFT) / 12, 2), 0)
                        Else
                            If rsSpec!rent_per_sqft = "Y" Then
                                rsNew.Fields.Item(Months(i)) = Round(FormatNumber(((rsSpec!rental_psf + rsSpec!rent_dollar) * rsSpec!SQFT) / 12, 2), 0)
                                xTempMonth = Round(FormatNumber(((rsSpec!rental_psf + rsSpec!rent_dollar) * rsSpec!SQFT) / 12, 2), 0)
                                MonthTotal(i) = MonthTotal(i) + Round(FormatNumber(((rsSpec!rental_psf + rsSpec!rent_dollar) * rsSpec!SQFT) / 12, 2), 0)
                                Total = Total + Round(FormatNumber(((rsSpec!rental_psf + rsSpec!rent_dollar) * rsSpec!SQFT) / 12, 2), 0)
                            Else
                                rsNew.Fields.Item(Months(i)) = Round(FormatNumber(((rsSpec!rental_psf * rsSpec!SQFT) + rsSpec!rent_dollar) / 12, 2), 0)
                                xTempMonth = Round(FormatNumber(((rsSpec!rental_psf * rsSpec!SQFT) + rsSpec!rent_dollar) / 12, 2), 0)
                                MonthTotal(i) = MonthTotal(i) + Round(FormatNumber(((rsSpec!rental_psf * rsSpec!SQFT) + rsSpec!rent_dollar) / 12, 2), 0)
                                Total = Total + Round(FormatNumber(((rsSpec!rental_psf * rsSpec!SQFT) + rsSpec!rent_dollar) / 12, 2), 0)
                            End If
                        End If
                    Else
                        rsNew.Fields.Item(Months(i)) = Round(FormatNumber((rsSpec!rental_psf * rsSpec!SQFT) / 12, 2), 0)
                        xTempMonth = Round(FormatNumber((rsSpec!rental_psf * rsSpec!SQFT) / 12, 2), 0)
                        MonthTotal(i) = MonthTotal(i) + Round(FormatNumber((rsSpec!rental_psf * rsSpec!SQFT) / 12, 2), 0)
                        Total = Total + Round(FormatNumber((rsSpec!rental_psf * rsSpec!SQFT) / 12, 2), 0)
                    End If
      
                    Found = True
                    
                End If
                
            End If
        Else
            If xType <> "free-rnt" Then
                If (Year(DateValue(CommDate)) = Val(xYear) And Month(DateValue(CommDate)) <= i) Or Year(DateValue(CommDate)) < xYear Then
                    
                    If rsSpec!add_tenant = "R" Then
                        rsNew!Desc = Trim(rsSpec!TName) + " (Renewal)"
                    Else
                        rsNew!Desc = rsSpec!TName + " (" + rsSpec!Tcode + ")"
                    End If
                    xTempMonth = 0
                    If (Year(DateAdd("m", 12, DateValue(CommDate))) = Val(xYear) And Month(DateAdd("m", 12, DateValue(CommDate))) <= i) Or Year(DateAdd("m", 12, DateValue(CommDate))) < xYear Then
                        If Not IsNull(rsSpec!annual_rent_step) Then
                            rsNew.Fields.Item(Months(i)) = Round(FormatNumber(((rsSpec!rental_psf + (rsSpec!rental_psf * (rsSpec!annual_rent_step / 100))) * rsSpec!SQFT) / 12, 2), 0)
                            xTempMonth = Round(FormatNumber(((rsSpec!rental_psf + (rsSpec!rental_psf * (rsSpec!annual_rent_step / 100))) * rsSpec!SQFT) / 12, 2), 0)
                            MonthTotal(i) = MonthTotal(i) + Round(FormatNumber(((rsSpec!rental_psf + (rsSpec!rental_psf * (rsSpec!annual_rent_step / 100))) * rsSpec!SQFT) / 12, 2), 0)
                            Total = Total + Round(FormatNumber(((rsSpec!rental_psf + (rsSpec!rental_psf * (rsSpec!annual_rent_step / 100))) * rsSpec!SQFT) / 12, 2), 0)
                        Else
                            If rsSpec!rent_per_sqft = "Y" Then
                                rsNew.Fields.Item(Months(i)) = Round(FormatNumber(((rsSpec!rental_psf + rsSpec!rent_dollar) * rsSpec!SQFT) / 12, 2), 0)
                                xTempMonth = Round(FormatNumber(((rsSpec!rental_psf + rsSpec!rent_dollar) * rsSpec!SQFT) / 12, 2), 0)
                                MonthTotal(i) = MonthTotal(i) + Round(FormatNumber(((rsSpec!rental_psf + rsSpec!rent_dollar) * rsSpec!SQFT) / 12, 2), 0)
                                Total = Total + Round(FormatNumber(((rsSpec!rental_psf + rsSpec!rent_dollar) * rsSpec!SQFT) / 12, 2), 0)
                            Else
                                rsNew.Fields.Item(Months(i)) = Round(FormatNumber(((rsSpec!rental_psf * rsSpec!SQFT) + rsSpec!rent_dollar) / 12, 2), 0)
                                xTempMonth = Round(FormatNumber(((rsSpec!rental_psf * rsSpec!SQFT) + rsSpec!rent_dollar) / 12, 2), 0)
                                MonthTotal(i) = MonthTotal(i) + Round(FormatNumber(((rsSpec!rental_psf * rsSpec!SQFT) + rsSpec!rent_dollar) / 12, 2), 0)
                                Total = Total + Round(FormatNumber(((rsSpec!rental_psf * rsSpec!SQFT) + rsSpec!rent_dollar) / 12, 2), 0)
                            End If
                        End If
                    
                    Else
                        rsNew.Fields.Item(Months(i)) = Round(FormatNumber((rsSpec!rental_psf * rsSpec!SQFT) / 12, 2), 0)
                        xTempMonth = Round(FormatNumber((rsSpec!rental_psf * rsSpec!SQFT) / 12, 2), 0)
                        MonthTotal(i) = MonthTotal(i) + Round(FormatNumber((rsSpec!rental_psf * rsSpec!SQFT) / 12, 2), 0)
                        Total = Total + Round(FormatNumber((rsSpec!rental_psf * rsSpec!SQFT) / 12, 2), 0)
                    End If
                    
                    Found = True
                    
                End If
            End If
        End If
        
DownHere:
    End If
    Next i%
    
    If Found Then

        MonthTotal(13) = MonthTotal(13) + Total
        Total = 0
    
        rsNew.Update
    
        xRow = xRow + 1
    Else

        rsNew.CancelUpdate
    End If
    
    rsSpec.MoveNext

Loop

rsSpec.Close

ICall clsDB.rsInit(rsMkt)

rsMkt.Source = ("SELECT * FROM mkt_assump WHERE prop_num = '" _
+ Format(PropNum, "00000") + "' AND year = '" + xYear + "'")

rsMkt.Open

Call clsDB.rsInit(rsSpec)

rsSpec.Source = ("SELECT * FROM lease WHERE prop_num = '" _
+ Format(PropNum, "00000") + "' AND year = '" + xYear + "' AND renewal_type IN ('S','X') AND expiration_date " _
+ " <= #12/31/" + Format(xYear) + "#")

rsSpec.Open

Do While Not rsSpec.EOF

    Found = False
    rsNew.AddNew
    rsNew!Year = xYear
    rsNew!prop_num = PropNum
    rsNew!acct_num = AcctNum
    rsNew!detail_num = Info.CreateDetailNum(xYear, PropNum)
    
    For i% = 1 To 12
        
        TempYear = Year(DateAdd("m", Round((Val(Format(100 - Val(Format(rsMkt!probability, "General Number")), "General Number")) / 100) * Val(Format(rsMkt!rollover, "General Number")), 0), rsSpec!expiration_date))
        TempMonth = Month(DateAdd("m", Round((Val(Format(100 - Val(Format(rsMkt!probability, "General Number")), "General Number")) / 100) * Val(Format(rsMkt!rollover, "General Number")), 0), rsSpec!expiration_date))
        
        If (i > TempMonth And xYear = TempYear) Or (TempYear = xYear - 1) Then
            rsNew!Desc = Trim(rsSpec!TName) + " (MKT)"

            MktAmt = ((((Val(Format(rsMkt!probability, "General Number"))) / 100) * Val(Format(rsMkt!mkt_rent_renew, "General Number")))) + (((100 - Val(Format(rsMkt!probability, "General Number"))) / 100) * Val(Format(rsMkt!mkt_rent_new, "General Number")))
            
            If TempYear = xYear - 1 And TempMonth < i Then
                If Not IsNull(rsMkt!inflation) Then
                    MktAmt = MktAmt * (1 + (rsMkt!inflation / 100))
                End If
            End If
                        
            Total = Total + Round(FormatNumber(MktAmt * rsSpec!SQFT / 12, 2), 0)
            rsNew.Fields.Item(Months(i)) = Round(FormatNumber(MktAmt * rsSpec!SQFT / 12, 2), 0)
            MonthTotal(i) = MonthTotal(i) + Round(FormatNumber(MktAmt * rsSpec!SQFT / 12, 2), 0)
            
            Found = True
            
        End If
         
    Next i%

    If Found Then
        '***Do Stuff***
        rsNew.Update
        
    Else
        rsNew.CancelUpdate
    End If
    
    rsSpec.MoveNext

Loop

xRow = xRow + 1
    
End Sub

[Chrissy]

I had to delete some of the code to get it to fit.... I got rid of all the DIM statements... just assume all variables are declared.

[Siddharth Rout]

Threads merged.

[techgnome]

from the example PDF....

Lets say you have a series of checkboxes and you
want to update the database with the checked data. If
the database currently holds Options 5, 9 & 13, how do
you programmatically go about replacing those options
with the new options 4, 5 & 12. Currently I am first
deleting all the rows that relate to Tenant 1 and then
replacing them with the new data. I am just wondering
if there is a better way of updating a table using this
logic.

By removing ALL entries and inserting the new ones...
Also... what about #8? it was checked but not in the example of data to save...

But at any rate, that's how we do that kind of stuff in the system... I personally dislike that but, it works... clear out the existing related data, even if some of the "new" records were already in the table, remove them anyways... then insert in the records...

delete from whatevertable where somefield = "Tenant 1"
insert into whatevertable Setting, somefield) values ("Option 5", "Tenant 1")
insert into whatevertable Setting, somefield) values ("Option 8", "Tenant 1")
insert into whatevertable Setting, somefield) values ("Option 9", "Tenant 1")
insert into whatevertable Setting, somefield) values ("Option 13", "Tenant 1")


-tg

[Chrissy]

from the example PDF....


By removing ALL entries and inserting the new ones...
Also... what about #8? it was checked but not in the example of data to save...

But at any rate, that's how we do that kind of stuff in the system... I personally dislike that but, it works... clear out the existing related data, even if some of the "new" records were already in the table, remove them anyways... then insert in the records...

delete from whatevertable where somefield = "Tenant 1"
insert into whatevertable Setting, somefield) values ("Option 5", "Tenant 1")
insert into whatevertable Setting, somefield) values ("Option 8", "Tenant 1")
insert into whatevertable Setting, somefield) values ("Option 9", "Tenant 1")
insert into whatevertable Setting, somefield) values ("Option 13", "Tenant 1")


-tg

Tenant 8 was an oversight in the example....

Deleting all entries first is what I am currently doing but don't particularly like it... I thought there might be a better way.

Thanks for the answer.