I have a problem loading a field using a function in ADVPL

Question

I need to load the field CK_XLOTE typing the code of the product in the field CK_PRODUTO, I am generating a function to perform this procedure, but it is not returning the lot in the field. Can someone help me.

Follow the code

#INCLUDE 'PROTHEUS.CH'
#INCLUDE 'PARMTYPE.CH'

User Function TESTE()

//MsgAlert(M->CK_PRODUTO)

Local cQuery        := ""
Local cLote         := ""

cQuery := " SELECT  TOP 1 "
cQuery += "         SB8.B8_LOTECTL, "
cQuery += "         SB8.B8_DTVALID "
cQuery += " FROM    " + RetSQLName("SB8") + "   AS SB8 "
cQuery += " WHERE   SB8.B8_FILIAL               = '" + xFilial("SB8") + "' "
cQuery += " AND     SB8.B8_LOCAL                = '01' "
cQuery += " AND     SB8.B8_DTVALID              > GETDATE() "
cQuery += " AND     SB8.B8_SALDO                > SB8.B8_EMPENHO "
cQuery += " AND     SB8.B8_PRODUTO              = '" + M->CK_PRODUTO + "' "
cQuery += " AND     SB8.D_E_L_E_T_              = '' "
cQuery += " ORDER BY SB8.B8_DTVALID "

If SELECT("SQL") > 0    
    dbSelectArea("SQL")     
    dbCloseArea()       
EndIf 

cQuery := ChangeQuery(cQuery)                                                              
dbUseArea(.T., 'TOPCONN', TCGENQRY(,,cQuery),"SQL", .F., .T.)       
dbSelectArea("SQL")

If SQL->(!EOF())

    cLote := SQL->B8_LOTECTL

EndIf

return(cLote)

Follow the trigger setting

Answer 1

Query is probably not bringing any record, due to mounting one or two conditions in the Query:

cQuery += " AND     SB8.D_E_L_E_T_ = '' "

When comparing the SB8.D_E_L_E_T_field, compare it to a string with a blank space (' ') instead of an empty string (''). The database understands that an empty string without spaces represents the NULL value -- and no record will meet this condition.

cQuery += " AND     SB8.B8_DTVALID              > GETDATE() "

When comparing the B8_VALID field in the Query, remember that a DATA field in Advpl is written to the database as a varchar(8), in YYYYMMDD format. So, make sure that this comparison between the date in character format and the Getdate() function actually accepts the current format, or if it is not necessary to make a CAST() from the B8_VALID field or even a CAST() from GETDATE() to return a string in the appropriate format for comparison.