x Want to participate? Join the group to interact
Options
  • Replies 7 replies
  • Subscribers 87 subscribers
  • Views 1187 views
  • Users 0 members are here
Browse Forums
Announcements
292
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
43
topics
90
replies
Payroll, Time Tracking, and Job Cost
775
topics
1k
replies
Reporting and Analytics
960
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
Steve Burks

How to find different record, change field value, and write the changed record back to the file

Posted By

I have a UDF checkbox on the GL_Account maintenance called UDF_DONATION.  What I want to do in the Table Pre-Write is to check to see if there is another GL Account record where this UDF_DONATION is already set to "Y".  If there is another record with the UDF_DONATION field set to "Y" I would like the script to change the UDF_DONATION on the GL Account that was found to have the UDF_DONATION field set to "Y" to "N".  Then save the current GL Account change through the standard process.

I have figured out how to find the existing record (not the current record since the script is being executed during the Pre-Write Object) where the UDF_DONATION is "Y".  I am using a SQL search to find this record.

The problem I am having is how to change the UDF_DONATION field from "Y" to "N" for the secondary GL Account.  

I have tried creating a second GL_Account_Bus object (I am not sure if I am doing this correctly or if this is the right thing to do.) and using the .FIND and even tried looping through the records and using the SetKey but the SetKey value fails.

I would appreciate any help.

Thanks

Steve

  • SetKey has to be used with the AccountKey, not Account value.

  • in reply to Kevin M

    Kevin,

    I have tried:

    .SetKeyValue("AccountKey$",AccountKeyVar)

    .SetKey()

    and

    .SetKey(AccountKeyVar)

    They both fail.  I have verified that that the AccountKeyVar is equal to the GL Account Key for the selected account.

    This is the code I am using to create the GL_Account_Bus object in my script..

    '* Get a handle to GL Account svc object where main table is GL_Account
    oGLAccount = oSession.GetObject("GL_Account_svc")
    If oGLAccount = 0 Then
        sMsg = "Could not GetObject GL_Account_svc - Last Error Msg = " & vbCRLF & oSession.LastErrorMsg
        retMsg = oSession.AsObject(oSession.UI).sMsgBox("", sMsg)
    Else
        Set oGLAccount = oSession.AsObject(oGLAccount)  
    End If
    I can use the .MOVEFIRST and also loop through the GL_Account_Records and use the .GetValue to check the AccountKey for each record.
    Steve
  • in reply to Steve Burks

    GL_Account_svc is the read only service object.  Use GL_Account_bus

  • in reply to Kevin M

    With service objects (_svc) you need to use .Find(accountKeyVar) with business objects (_bus) you can use .SetKey(accountKeyVar)

    But as Kevin stated if you need to modify values in the GL Account record you'll need to use the business object not the service object.

    E

  • in reply to jepritch

    Sorry I copied the wrong section of code....

    Here is how I am creating the GL_Account_Bus object:

    '*  Creating a new separate GL Business Object to write second record when needed.
    GLBUSObj = oSession.GetObject("GL_Account_bus")
    SET GLBUSObj = oScript.AsObject(GLBUSObj)
    kfretval = GLBUSObj.MoveFirst
    Then this is what I am doing to try to change the record that I need to change:
            msgbox ("KFSQLDonationsAccountKey = " & KFSQLDonationsAccountKey)
            KFretval = GLBUSObj.SetKey(KFSQLDonationsAccountKey)
            msgbox ("Set Key = " & KFretval)
            KFretval = GLBUSObj.SetValue("UDF_DONATIONS_COGS_ACCOUNT$", "N")
            msgbox ("Set Value = " & KFretval)
            KFretVal = GLBUSObj.Write()
            msgbox ("Write = " & KFretval)
    using msgbox for tracing.  the KFSQLDonationsAccountKey is the Account key of the record I am trying to change.  The SetKey returns a 0.  The SetValue returns 1.  The Write returns 0.
    Thanks
    Steve
  • in reply to Steve Burks

    I always check the value of the GetObject, to ensure there is no permission issue.

    oAccount = oSession.GetObject("GL_Account_bus")
    if oAccount <> 0 then
        Set oAccount = oSession.AsObject(oAccount)
    else    
        retVal = oSession.AsObject(oSession.UI).MessageBox("", "Access to GL_Account_bus is required for the ... script to work.")
        exit sub
    end if

  • in reply to Kevin M

    I changed my code to do exactly as you describe above to setup the GL_Account_Bus object and the rest of my script runs.  I tried to msgbox the oAccont to check the Handlle number and I get an error (Again maybe not understanding the script stuff).  Still not doing the .SetKey and therefore not doing the .Write.

    Does it have something to do with the fact that the script is running in the Prewrite of the GL Maintenance and the current record is being edited?  I jus thought that by using a second instance of the GL_Account_BUS obj that I would be able to do this quite simply.

    Steve

*Community Hub is the new name for Sage City