Inserting a record

[SleepingGiant]

I'm having difficulty inserting a record from one inventory table to a structurally identical table if it does not exist. Both tables have 6 columns, "ID", "Description", "Average Cost", "Amount", "Vendor" and "VenStockNo." I have managed to get it to locate and add the "amount" (quantity) from one to the other if it does exist, however, I can't seem to get it to insert a record to add new items if it doesn't exist. Here's what I have so far...

method DoReportD(Print Logical)
var
TC1, TC2 TCursor
endVar

if TC1.open(":WORKefctWSE.DB&quot and
TC2.open("RIV:!DfctWse.DB&quot then
TC1.edit()
IF TC1.Locate ("ID", TC2."ID&quot then

TC1."Amount" = TC1."Amount"+TC2."Amount"
TC1.endEdit()
endif
IF NOT TC1.Locate ("ID", TC2."ID&quot then
TC1.InsertRecord()
TC1."ID"=TC2."ID"
TC1."Description"=TC2."Description"
TC1."Average Cost"=TC2."Average Cost"
TC1."Amount"=TC2."Amount"
TC1."Vendor"=TC2."Vendor"
TC1."VenStockNo"=TC2."VenStockNo"

endIf

TC1.endEdit()
TC1.close()
TC2.close()
endIf
endmethod

Any help is appreciated. TIA

 

[Langley]

Try making it part of an ELSE statement instead of it's own IF loop.

method DoReportD(Print Logical)
var
    TC1, TC2 TCursor
endVar

  if TC1.open(":WORK:DefctWSE.DB") and
    TC2.open(":PRIV:!DfctWse.DB") then  
    TC1.edit()  

    IF TC1.Locate ("ID", TC2."ID") then
         TC1."Amount" = TC1."Amount"+TC2."Amount"
         TC1.endEdit()
      ELSE
        TC1.InsertRecord()
        TC1."ID"=TC2."ID" ; I assume you want the first record in TC2 (make sure it's not empty)
        TC1."Description"=TC2."Description"
        TC1."Average Cost"=TC2."Average Cost"
        TC1."Amount"=TC2."Amount"
        TC1."Vendor"=TC2."Vendor"
        TC1."VenStockNo"=TC2."VenStockNo"
    ENDIF
  endIf
     
    TC1.endEdit()
    TC1.close()                 
    TC2.close()
endIf
endmethod

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[Langley]

Just a point of clarification, the newly inserted record will have values identical with the 1st record of the RIV:!DfctWse.DB table (it can't be blank, or you get nothing but an empty record). If you have some set values you want to add for new records, then explicitly state them in the ELSE clause (i.e. TC1."ID"="1234-T", TC1."Description"="Jet engine", etc...)


Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[SleepingGiant]

Thanks for the help, Langley. However that does not seem to work either. I did attempt using ELSE prior to my post with no luck. I cleaned up my code a bit and this is where it is at now:

method DoReportD(Print Logical)
var
TC1, TC2 TCursor
endVar

TC1.open(":WORKefctWSE.DB&quot
TC2.open("RIV:!DfctWse.DB&quot
TC1.edit()
IF TC1.Locate ("ID", TC2."ID&quot THEN
TC1."Amount" = TC1."Amount"+TC2."Amount"

ELSE
TC1.InsertRecord()
TC1."ID"=TC2."ID"
TC1."Description"=TC2."Description"
TC1."Average Cost"=TC2."Average Cost"
TC1."Amount"=TC2."Amount"
TC1."Vendor"=TC2."Vendor"
TC1."VenStockNo"=TC2."VenStockNo"
ENDIF

TC1.endEdit()
TC1.close()
TC2.close()
endmethod

This code accomplishes half of what I want to do: add's quantity to the "Amount" field in the TC1 table if the "ID" entry exists in both TC1 & TC2, but does not insert the record from TC2 into TC1 when the "ID" doesn't exist in TC1. If it makes a difference, TC1 is Keyed on the "ID" field and TC2 is a tempary table created from a query and not Keyed at all.

Thanks again, but any other suggestions??

Walt

 

[Langley]

Are you verifying that the TC2 table has data? That would cause that sort of problem. You might test for "if TC2.isEmpty()" at the start of the ELSE clause.

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[SleepingGiant]

I have verified that TC2 table has data prior to running the formula and after running it. TC2 has 6 entries, one of which exists in TC1. TC1 has 11 entries. After the method is executed I should have 16 entries in TC1 with the common "ID" having the "Amount" increased. But that's not happening.

Thanks again.

Walt

 

[Langley]

Is this supposed to be in a scan loop? That's the only way it would work like you want it to. The way it is, it would only look at the first record in TC2 no matter how many times you execute it. Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[SleepingGiant]

I'm not sure about "scan loop" but I'm trying to get TC1 to be a product of both tables. More than just the first record of TC2.

Walt

 

[Langley]

Try this:

method DoReportD(Print Logical)
var
    TC1, TC2 TCursor
endVar

if isEmpty(":PRIV:!DfctWse.DB") then
   msgInfo("Empty","No records in table")
   return
endif

TC1.open(":WORK:DefctWSE.DB") 
TC2.open(":PRIV:!DfctWse.DB")   
TC1.edit()  

scan tc2:

     IF TC1.Locate ("ID", TC2."ID") THEN
        TC1."Amount" = TC1."Amount"+TC2."Amount"
     
     ELSE 
        TC1.InsertRecord()
        TC1."ID"=TC2."ID"
        TC1."Description"=TC2."Description"
        TC1."Average Cost"=TC2."Average Cost"
        TC1."Amount"=TC2."Amount"
        TC1."Vendor"=TC2."Vendor"
        TC1."VenStockNo"=TC2."VenStockNo"
     ENDIF
    
    TC1.endEdit()
    TC1.close()                 
    TC2.close()

endscan

endmethod

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[Langley]

OOOOPS!!!!

Forgot something!

Take this out of the scan loop:

TC1.endEdit()
TC1.close()
TC2.close()

and add it on th eline after the endScan statement.

Sorry, about that.

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[SleepingGiant]

Thanks Langley. That works. You're the man! I was getting close after I researched "scan" commnad, but had scanned TC1 instead of TC2 and had 285,000+ additions to the common record before I stopped it LOL

Thanks again.

Walt

 

[Langley]

285,000 - wow! But I think we've all been there

Glad it worked,

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us