# Adding and Updating Records using code

**URL:** <https://clarionhub.com/t/adding-and-updating-records-using-code/5692>\
**Category:** questions\
**Tags:** c11\
**Created:** [November 17, 2022, 1:56pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692 "2022-11-17T13:56:54Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![DonnEdwards](https://clarionhub.com/user_avatar/clarionhub.com/donnedwards/32/1047_2.png) [@DonnEdwards](https://clarionhub.com/u/DonnEdwards)\
**Post date:** [November 17, 2022, 1:56pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/1 "2022-11-17T13:56:55Z")

</div>

I have a table called “sgCategories” that has the following structure:

> ```
> FILE,DRIVER('TOPSPEED')
> 
> ```
> 
> SCA:SGCATEGORYPK KEY(+CompanyId,+id),NOCASE,PRIMARY  
> Record RECORD  
> SCA:CompanyId LONG  
> SCA:Categoryid LONG  
> SCA:Description STRING(100)  
> SCA:DEFAULTYN BYTE  
> SCA:DATEMODIFIED DATE  
> SCA:TIMEMODIFIED TIME  
> SCA:DATECREATED DATE  
> SCA:TIMECREATED TIME  
> END  
> END

I want to import data from a JSON file, which I put in a queue. I have left that part out of the this code, for simplicity. Instead, I am just generating a random number of records and inserting the record if not found, or updating it if found.

> ```
> Access:sgCategory.Init()
> Access:sgCategory.Open() ! Open the table
> Access:sgCategory.UseFile() ! Use the table
> Access:sgCategory.ClearKey(sca:sgCategoryPK) ! Assure all Key fields have lowest value
> ! sca:sgCategoryPK = sca:CompanyID + sca:CategoryID
> SET(sca:sgCategoryPK,sca:sgCategoryPK) ! Choose the PK index
> sca:CompanyID = glo:coID ! Global value for current company
> LOGOUT(1,sgCategory) ! Speed up transactions?
> LOOP i = 1 TO RANDOM(50,250) ! Test values loop
> sca:CategoryID = i
> if Access:sgCategory.Fetch(sca:sgCategoryPK) = Level:Benign then ! Found
> !// Update the date modified, leave the other fields alone
> sca:DateModified = TODAY() ! Todays date
> sca:TimeModified = CLOCK() ! Now
> else ! Not found
> !// Insert a new record and update its fields where applicable
> Access:sgCategory.Insert()
> Access:sgCategory.PrimeRecord()
> sca:CompanyID = glo:coID
> sca:CategoryID = i
> sca:Description = 'Category ' & i
> !sca:DefaultYN = False ! False by default anyway
> sca:DateModified = TODAY() ! Todays date
> sca:TimeModified = CLOCK() ! Now
> sca:DateCreated = TODAY()
> sca:TimeCreated = CLOCK()
> end
> Access:sgCategory.Update() ! Update the record
> END ! Test values loop
> COMMIT ! Make sure the data is all written to the table
> Access:sgCategory.Close() ! Close the table
> 
> ```

Is there anything I have left out or anything that I should change to improve this code? Do I need the “PrimeRecord” command or the “Commit” command, for example?

Any advice would be most welcome

---

<div class="post-metadata">

**Author:** ![DonnEdwards](https://clarionhub.com/user_avatar/clarionhub.com/donnedwards/32/1047_2.png) [@DonnEdwards](https://clarionhub.com/u/DonnEdwards)\
**Post date:** [November 17, 2022, 4:49pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/2 "2022-11-17T16:49:52Z")

</div>

Follow-up question: I want to have only one record with DefaultYN = True.

Let’s say I edit CategoryID = 33 and set it to true. How do I do

> UPDATE sgCategory SET DefaultYN = 0 WHERE CategoryID \<\> 33 AND DefaultYN = True

I know how to do this on a SQL table, but not a TPS table, other than by looping through all the records, which isn’t terribly efficient.

---

<div class="post-metadata">

**Author:** ![Rick\_UpperPark](https://clarionhub.com/user_avatar/clarionhub.com/rick_upperpark/32/346_2.png) [@Rick\_UpperPark](https://clarionhub.com/u/Rick_UpperPark)\
**Post date:** [November 17, 2022, 4:59pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/3 "2022-11-17T16:59:53Z")

</div>

You need an index on DefaulYN to quickly find the row that is marked as True. Otherwise, you have to loop through the table.

---

<div class="post-metadata">

**Author:** ![Rick\_UpperPark](https://clarionhub.com/user_avatar/clarionhub.com/rick_upperpark/32/346_2.png) [@Rick\_UpperPark](https://clarionhub.com/u/Rick_UpperPark)\
**Post date:** [November 17, 2022, 5:37pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/5 "2022-11-17T17:37:30Z")

</div>

> [@DonnEdwards](#):
>
> ```auto
> if Access:sgCategory.Fetch(sca:sgCategoryPK) = Level:Benign then ! Found
> !// Update the date modified, leave the other fields alone
> sca:DateModified = TODAY() ! Todays date
> sca:TimeModified = CLOCK() ! Now
> else ! Not found
> !// Insert a new record and update its fields where applicable
> Access:sgCategory.Insert()
> Access:sgCategory.PrimeRecord()
> sca:CompanyID = glo:coID
> sca:CategoryID = i
> sca:Description = 'Category ' & i
> !sca:DefaultYN = False ! False by default anyway
> sca:DateModified = TODAY() ! Todays date
> sca:TimeModified = CLOCK() ! Now
> sca:DateCreated = TODAY()
> sca:TimeCreated = CLOCK()
> end
> Access:sgCategory.Update()   
> 
> ```

To me that is an odd way to go about it. Doing the Insert of an empty row then doing an update.  
The PrimeRecord is not technically necessary. It will set any default values you have set in the dictionary for you.  
If you have dictionary validation setup, your Insert call will likely because the tables record buffer is blank when you call the Insert. Also, if there is a primary unique key on CategoryID, then I’d think the Insert would fail because or add a row with zero for the ID. Which means only one can insert can happen at a time (multi-user environment), if this logic is used.  
If you have the LOGOUT, then the COMMIT is necessary. Remember with TPS, the table will be locked from other Insert/Updates during the transaction.  
I’d move the Update into the IF after successful Fetch and move the Insert to the bottom of the ELSE branch.

---

<div class="post-metadata">

**Author:** ![DonnEdwards](https://clarionhub.com/user_avatar/clarionhub.com/donnedwards/32/1047_2.png) [@DonnEdwards](https://clarionhub.com/u/DonnEdwards)\
**Post date:** [November 17, 2022, 6:26pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/6 "2022-11-17T18:26:15Z")

</div>

Thank you. Now I have:

> ```
> Access:sgCategory.Init()
> Access:sgCategory.Open() ! Open the table
> Access:sgCategory.UseFile() ! Use the table
> Access:sgCategory.ClearKey(sca:sgCategoryPK) ! Assure all Key fields have lowest value
> ! sca:sgCategoryPK = sca:CompanyID + sca:CategoryID
> SET(sca:sgCategoryPK,sca:sgCategoryPK) ! Choose the PK index
> sca:CompanyID = glo:coID ! Global value for current company
> LOGOUT(1,sgCategory) ! Speed up transactions?
> LOOP i = 1 TO RANDOM(50,250) ! Test values loop
> sca:CategoryID = i
> if Access:sgCategory.Fetch(sca:sgCategoryPK) = Level:Benign then ! Found
> !// Update the date modified, leave the other fields alone
> sca:DateModified = TODAY() ! Todays date
> sca:TimeModified = CLOCK() ! Now
> Access:sgCategory.Update() ! Update the record
> else ! Not found
> !// Insert a new record and update its fields where applicable
> Access:sgCategory.PrimeRecord()
> sca:CompanyID = glo:coID
> sca:CategoryID = i
> sca:Description = 'Category ' & i
> !sca:DefaultYN = False
> sca:DateModified = TODAY() ! Todays date
> sca:TimeModified = CLOCK() ! Now
> sca:DateCreated = TODAY()
> sca:TimeCreated = CLOCK()
> Access:sgCategory.Insert()
> end
> END ! Test values loop
> COMMIT ! Make sure the data is all written to the table
> Access:sgCategory.Close() ! Close the table
> 
> ```

I hope I followed your recommendations correctly

---

<div class="post-metadata">

**Author:** ![DonnEdwards](https://clarionhub.com/user_avatar/clarionhub.com/donnedwards/32/1047_2.png) [@DonnEdwards](https://clarionhub.com/u/DonnEdwards)\
**Post date:** [November 17, 2022, 6:34pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/7 "2022-11-17T18:34:42Z")

</div>

> [@Rick\_UpperPark](#):
>
> You need an index on DefaulYN to quickly find the row that is marked as True. Otherwise, you have to loop through the table.

I created an index key called sca:sgCatDefaultYNKey that just uses the DefaultYN field.

I created 3 local variables in the edit form:

> loc:CompanyID LONG  
> loc:CategoryID long  
> loc:DefaultYN Byte

I set loc:DefaultYN = False when the window opens.

In the **ThisWindow.Update** procedure I have added:

> ```
> CODE
> ! Start of "WindowManager Method Executable Code Section"
> ! [Priority 2500]
> sca:DateModified = TODAY() ! We have changed this record
> sca:TimeModified = CLOCK()
> ! Parent Call
> PARENT.Update
> ! [Priority 6300]
> loc:CompanyID = sca:CompanyID
> loc:CategoryID = sca:CategoryID
> loc:DefaultYN = sca:DefaultYN
> 
> ```

and then in the **ThisWindow.TakeCompleted** procedure I have

> ```
> ! Parent Call
> ReturnValue = PARENT.TakeCompleted()
> ! [Priority 6300]
> if loc:DefaultYN then ! Default
> ! Unmark all other records
> Access:sgCategory.Open() ! Open the table
> Access:sgCategory.UseFile() ! Use the table    
> Access:sgCategory.ClearKey(sca:sgCatDefaultYNKey) ! Assure all Key fields have lowest value
> sca:DefaultYN = True
> LOGOUT(1,sgCategory)                       
> SET(sca:sgCatDefaultYNKey,sca:sgCatDefaultYNKey)
> LOOP UNTIL Access:sgCategory.Next() 
> if (sca:CategoryID <> loc:CategoryID) and (sca:DefaultYN = true) then ! found
> sca:DefaultYN = False
> sca:DateModified = TODAY()
> sca:TimeModified = CLOCK()
> Access:sgCategory.Update()
> end ! found
> END ! LOOP
> COMMIT
> Access:sgCategory.Close() ! Close the table 
> end ! Default
> RETURN ReturnValue
> 
> ```

This seems to work as required. Have I broken anything or put the code in the wrong place? I’m getting to learn how the FileManager stuff works.

---

<div class="post-metadata">

**Author:** ![Rick\_UpperPark](https://clarionhub.com/user_avatar/clarionhub.com/rick_upperpark/32/346_2.png) [@Rick\_UpperPark](https://clarionhub.com/u/Rick_UpperPark)\
**Post date:** [November 17, 2022, 6:47pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/8 "2022-11-17T18:47:59Z")

</div>

In your TakeCompleted logic you wan to wrap that in IF ReturnValue = Level:Benign. That indicates the update/insert was successful.  
If this is an update form for sgCategory, you don’t need the .Open/UseFile/Close calls (they aren’t hurting anything).

I’m not sure the Update method is what you think. That is not where the row is updated.That’s a method called related to UI like the UPDATE statement in the language. It gets called frequently before objects in the form do something and updates the object with current values from the UI (selected rows in a browse, etc.)

If you want to set the modified date time, I’d use the triggers in the data dll global embeds. If you don’t want to do that, then I’d put the code to update the modified date/time in the TakeComplete method before the parent call.

---

<div class="post-metadata">

**Author:** ![seanh](https://clarionhub.com/letter_avatar_proxy/v4/letter/s/4af34b/32.png) [@seanh](https://clarionhub.com/u/seanh)\
**Post date:** [November 17, 2022, 10:02pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/9 "2022-11-17T22:02:38Z")

</div>

Donn  
A slight problem might be that the .Fetch() will clear the buffer if it fails. See

> **[fetch\_get\_a\_specific\_record\_by\_key\_value\_.htm \[Clarion Community Help\]](http://www.clarion.help/doku.php?id=fetch_get_a_specific_record_by_key_value_.htm)**

And you set the company field Only Once!

---

<div class="post-metadata">

**Author:** ![anon23294430](https://clarionhub.com/user_avatar/clarionhub.com/anon23294430/32/3829_2.png) [@anon23294430](https://clarionhub.com/u/anon23294430)\
**Post date:** [November 18, 2022, 12:42am UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/10 "2022-11-18T00:42:58Z")

</div>

> [@DonnEdwards](#):
>
> `Access:sgCategory.PrimeRecord()`

You dont need this as the autonumbered fields are not being used anywhere else before an insert() and insert() will prime the record before saving it anyway.

You dont have any error checking on some of your abc code

eg

> [@DonnEdwards](#):
>
> LOGOUT(1,sgCategory)  
> Access:sgCategory.Update()  
> `Access:sgCategory.Insert()`  
> Access:sgCategory.Close()

And will your logout complete in 1 second considering the random loop?  
LOOP i = 1 TO RANDOM(50,250)

I use

```
Logout(Seconds,File)
IF ErrorCode()
   !Handle the slowness
Else
    LogoutTime = Clock()
End

Set(Key,Key)
Loop Until Access:File.Next() <> Level:Benign
   !Some Processing
    IF Clock() => LogoutTime + (Seconds - 5 seconds) !Doesnt work past midnight, you need to handle that yourself.
         Commit
         IF ErrorCode()
              !Handle the commit error
         End
         Logout(Seconds,File)
          IF ErrorCode()
              !HAndle the logout error
          Else
               LogoutTime = Clock()
           End
     End
End
Commit
IF ErrorCode()
!Handle the commit error
End

```

Obviously the way people code is an opinion though.

---

<div class="post-metadata">

**Author:** ![DonnEdwards](https://clarionhub.com/user_avatar/clarionhub.com/donnedwards/32/1047_2.png) [@DonnEdwards](https://clarionhub.com/u/DonnEdwards)\
**Post date:** [November 18, 2022, 8:45am UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/11 "2022-11-18T08:45:54Z")

</div>

I appreciate all the comments, and will take appropriate action to fix the code.

I put all the Open/UseFile/Close statements because I am modifying records **other than** the one open in the edit form, so I waited until the last possible minute.

I will re-read the .Update instructions in the manual. I’m learning a great deal. Thanks!

---

<div class="post-metadata">

**Author:** ![anon23294430](https://clarionhub.com/user_avatar/clarionhub.com/anon23294430/32/3829_2.png) [@anon23294430](https://clarionhub.com/u/anon23294430)\
**Post date:** [November 18, 2022, 9:35am UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/12 "2022-11-18T09:35:24Z")

</div>

A long time ago Russ Egen did a lesson possibly his Understanding Classes in Hants,UK which @Mark_Sarson organised or an article possibly in Clarion mag on how to override the ABC error messages and throw your own more intuitive error message. If you can track that down, you can use the ABC error class to provide more feedback to users. I think from memory it involved overriding the messages in ABError.TRN file. You can see from code posted by others, its not something used very often, as most code posted still uses Message().

fwiw.

---

<div class="post-metadata">

**Author:** ![Mark\_Sarson](https://clarionhub.com/user_avatar/clarionhub.com/mark_sarson/32/6132_2.png) [@Mark\_Sarson](https://clarionhub.com/u/Mark_Sarson)\
**Post date:** [November 18, 2022, 11:29am UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/13 "2022-11-18T11:29:22Z")

</div>

Hi Donn

The training Richard remembers was based on Russ’s book, Programming Objects in Clarion.

Since Russ’s passing, it has been kindly open sourced, and the repository with PDF can be [found here](https://github.com/mriffey/RADFusionObjectsInClarion).

---

<div class="post-metadata">

**Author:** ![DonnEdwards](https://clarionhub.com/user_avatar/clarionhub.com/donnedwards/32/1047_2.png) [@DonnEdwards](https://clarionhub.com/u/DonnEdwards)\
**Post date:** [November 18, 2022, 12:26pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/15 "2022-11-18T12:26:05Z")

</div>

@Mark_Sarson @anon23294430 Thanks, I will take a look. Bruce’s ABC book also talks about error handling, and I will definitely improve my code with error checking and reporting. I just want to get the basic commands right first.

> [@Rick\_UpperPark](#):
>
> I’m not sure the Update method is what you think.

> The Update method changes (rewrites) the current record. The Update method handles any errors that occur while changing the record.  
> The TryUpdate method provides a slightly different (manual) alternative for changing records.

From the help: ABC Library Reference \> FileManager \> FileManager Methods \> Update  
Not to be confused with UPDATE (write from screen to USE variables)

---

<div class="post-metadata">

**Author:** ![jslarve](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/e480ec/32.png) [@jslarve](https://clarionhub.com/u/jslarve)\
**Post date:** [November 18, 2022, 2:47pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/16 "2022-11-18T14:47:02Z")

</div>

I think there’s stuff he he wrote about that on clarionmag.

---

<div class="post-metadata">

**Author:** ![DonnEdwards](https://clarionhub.com/user_avatar/clarionhub.com/donnedwards/32/1047_2.png) [@DonnEdwards](https://clarionhub.com/u/DonnEdwards)\
**Post date:** [November 18, 2022, 2:54pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/17 "2022-11-18T14:54:43Z")

</div>

> [@Rick\_UpperPark](#):
>
> I’d use the triggers in the data dll global embeds

Where do I find these? Not heard of this before

---

<div class="post-metadata">

**Author:** ![Rick\_UpperPark](https://clarionhub.com/user_avatar/clarionhub.com/rick_upperpark/32/346_2.png) [@Rick\_UpperPark](https://clarionhub.com/u/Rick_UpperPark)\
**Post date:** [November 18, 2022, 3:18pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/18 "2022-11-18T15:18:44Z")

</div>

Yes, that is correct. But the code you posted is in the ThisWindowManager.Update method, not a file manager method.

---

<div class="post-metadata">

**Author:** ![Rick\_UpperPark](https://clarionhub.com/user_avatar/clarionhub.com/rick_upperpark/32/346_2.png) [@Rick\_UpperPark](https://clarionhub.com/u/Rick_UpperPark)\
**Post date:** [November 18, 2022, 3:26pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/19 "2022-11-18T15:26:58Z")

</div>

In the Global Embeds of your “data” DLL for a multi-DLL solution or in the Global Embeds of your EXE for a single EXE solution, open the Global Objects → ABC Objects → File Managers branch. You will see all of the tables in your dictionary’s file manager objects listed.  
You’ll see methods PreInsert, PreUpdate, PreDelete and their Post… equivalent. Any code you put in the Pre methods will execute before a row is inserted, updated, etc. This ensures the code executes no matter where in your code you issue the command.  
You can also put code in the dictionary that will generate into these methods, but I’m not a fan of that because the editor for the trigger code in the dictionary isn’t that nice.

 ![image](https://clarionhub-uploads.s3.eu-north-1.amazonaws.com/original/2X/4/46fb30c033a2780a60bebe901f34ea67929a8391.png)

---

<div class="post-metadata">

**Author:** ![Mark\_Sarson](https://clarionhub.com/user_avatar/clarionhub.com/mark_sarson/32/6132_2.png) [@Mark\_Sarson](https://clarionhub.com/u/Mark_Sarson)\
**Post date:** [November 18, 2022, 3:37pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/20 "2022-11-18T15:37:49Z")

</div>

To be clear, this can be achieved in the trigger for tables in your dictionary. Probably easier for remembering:

 ![image](https://clarionhub-uploads.s3.eu-north-1.amazonaws.com/original/2X/6/6843427123ff2c764afed8ef85a16f69f4a38622.png)

A NOTE: For SQL folks, these are client-side triggers executed from the program, and not triggers executed on the back end.

---

<div class="post-metadata">

**Author:** ![Rick\_UpperPark](https://clarionhub.com/user_avatar/clarionhub.com/rick_upperpark/32/346_2.png) [@Rick\_UpperPark](https://clarionhub.com/u/Rick_UpperPark)\
**Post date:** [November 18, 2022, 3:47pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/21 "2022-11-18T15:47:28Z")

</div>

Yeah, it is available in the dictionary.  
I really don’t like the editor in the dictionary. It isn’t full features, no code completion, etc.

---

<div class="post-metadata">

**Author:** ![Mark\_Sarson](https://clarionhub.com/user_avatar/clarionhub.com/mark_sarson/32/6132_2.png) [@Mark\_Sarson](https://clarionhub.com/u/Mark_Sarson)\
**Post date:** [November 18, 2022, 3:49pm UTC](https://clarionhub.com/t/adding-and-updating-records-using-code/5692/22 "2022-11-18T15:49:55Z")

</div>

A tip for that issue is using

```auto
INCLUDE('Triggers.clw','SectionName'),ONCE 

```

Put this in the DCT, then you can edit Triggers.clw in your favorite editor of choice.

I find this easier than the embeds as it separates out the triggers from the APP and the DCT.

[Next page](https://clarionhub.com/t/adding-and-updating-records-using-code/5692.md?page=2)