• Welcome, Guest. Please login.
 
May 26, 2019, 05:20:46 am

News:

Welcome to the SQLitening support forums!

Recent posts

Pages1 2 3 ... 10
1
You've got Questions? We've got Answers! / Grouping and Summing Like Item...
Last post by Fredrick Ughimi - May 25, 2019, 08:52:21 pm
Hello,

I want to group and sum like items. For instance, from the list below:

Cash 1,500.00
Cash 2,500.00
Account Receivable 2,000.00
Account Receivable 3,000.00
Interest Receivable 4,500.00

I  want to get:

Cash 4,000.00
Account Receivable 5,000.00
Interest Receivable 4,500.00

using SQL statement. Grouping and summing like items.

I tried:

Code Select
slSel "Select * From tblJournalEntry WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Equity' GROUP BY AccountName HAVING SUM(Debit)"

Its grouping, but did not summing. The summing part is where I am having issues.
         
2
You've got Questions? We've got Answers! / Re: Tricky Save Routine
Last post by cj - May 21, 2019, 02:34:48 pm
Quote from: undefinedslExe "Begin"
  slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
  & $Nul & m_sNarration & $Nul & m_sAccount1 & $Nul & m_sDescription1 & $Nul & m_sDebit1 _
  & $Nul & m_sCredit1)
slExe "End"
     
slExe "Begin"
  slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
  & $Nul & m_sNarration & $Nul & m_sAccount2 & $Nul & m_sDescription2 & $Nul & m_sDebit2 _
  & $Nul & m_sCredit2)
slExe "End"
slExe "Begin Exclusive"
 slExe slBuildInsertOrUpdate("tblJournalEntry",(CHR$(SQLiteDate(Date),0,Ref,0,Narr,0,Acct1,0,Desc1,0,Debit1,0,Credit1)))
 slExe slBuildInsertOrUpdate("tblJournalEntry",(CHR$(SQLiteDate(Date),0,Ref,0,Narr,0,Acct2,0,Desc2,0,Debit2,0,Credit2)))
slexe "End"           
3
You've got Questions? We've got Answers! / Re: Tricky Save Routine
Last post by Fredrick Ughimi - May 02, 2019, 09:53:55 am
Thank you, Cj.

Your last post gave me an idea that works. Using the same table twice. Not the most elegant code, but it works.
It does what I want.

Code Select
m_sDate = VD_GetText (nCbHndl, %ID_FRMJOURNALENTRY_DPKDATE)
             
m_sReference = VD_GetText (nCbHndl, %ID_FRMJOURNALENTRY_TXTREFERENCE)
If Trim$(m_sReference) = "" Then
      MsgBox "Please, enter Reference", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
      Control Set Focus nCbHndl, %ID_FRMJOURNALENTRY_TXTREFERENCE
      Exit Method
End If
               
m_sNarration = VD_GetText (nCbHndl, %ID_FRMJOURNALENTRY_TXTNARRATION)
If Trim$(m_sNarration) = "" Then
      MsgBox "Please, enter Narration", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
      Control Set Focus nCbHndl, %ID_FRMJOURNALENTRY_TXTNARRATION
      Exit Method
End If
             
m_sAccount1 = MLG_Get(hJournalGrid, 1, 1)
m_sDescription1 = MLG_Get(hJournalGrid, 1, 2)
m_sDebit1 = MLG_Get(hJournalGrid, 1, 3)
m_sCredit1 = MLG_Get(hJournalGrid, 1, 4)   
             
m_sAccount2 = MLG_Get(hJournalGrid, 2, 1)
m_sDescription2 = MLG_Get(hJournalGrid, 2, 2)
m_sDebit2 = MLG_Get(hJournalGrid, 2, 3)
m_sCredit2 = MLG_Get(hJournalGrid, 2, 4)
             
slExe "Begin"
   slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
   & $Nul & m_sNarration & $Nul & m_sAccount1 & $Nul & m_sDescription1 & $Nul & m_sDebit1 _
   & $Nul & m_sCredit1)
slExe "End"
       
slExe "Begin"
   slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
   & $Nul & m_sNarration & $Nul & m_sAccount2 & $Nul & m_sDescription2 & $Nul & m_sDebit2 _
   & $Nul & m_sCredit2)
slExe "End"

MLG_PUT hJournalGrid,6,3,TotalCredit(hJournalGrid),0
MLG_PUT hJournalGrid,6,4,TotalDebit(hJournalGrid),0

? "Record Saved!", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
4
You've got Questions? We've got Answers! / Re: Tricky Save Routine
Last post by Fredrick Ughimi - May 02, 2019, 05:37:25 am
Hello Cj,

QuoteI don't know what column/row from the grid to put into the table columns.

All columns of the grid and all rows containing records because the rows could contain up to 3 records sometimes, but most of the times 2 records.

Kind regards.

5
You've got Questions? We've got Answers! / Re: Tricky Save Routine
Last post by Fredrick Ughimi - May 01, 2019, 05:25:14 pm
Hello Cj,

Attached JournalEntry.jpgis my form.
6
You've got Questions? We've got Answers! / Re: Tricky Save Routine
Last post by Fredrick Ughimi - May 01, 2019, 10:39:07 am
Thanks CJ. The thing is the values entered in the textboxes (Date, Narration, Reference) is usually a record, while the values entered on the grid (Account, Description, Credit, Debit) are usually two records or more per transaction. That is doing the double entry in Journal Entry of Accounting.

I would post a picture of my form in my next post whenever electricity comes on.

Best regards.
7
You've got Questions? We've got Answers! / Re: Tricky Save Routine
Last post by Fredrick Ughimi - April 30, 2019, 06:22:57 pm
Hello CJ,

Thanks for the heads up and great code as always. Since you did not use MLG and Textbox in your codes, it is a bit difficult to follow and adapt.

Regards.

8
You've got Questions? We've got Answers! / Re: Tricky Save Routine
Last post by Fredrick Ughimi - April 28, 2019, 10:26:24 pm
This saves but giving undesirable results. Three rows instead of two. Repeating the second row:

Code Select
rows = 2
cols  =7
 
RowStart=1    'select first row from mlg
RowEnd=2     'select last  row from mlg
ColStart=1    'need entire record
ColEnd=cols
ReDim FromGrid(RowStart To RowEnd,ColStart To ColEnd) As String 'dim array
MLG_GETEx hJournalGrid,FromGrid()                                      'fill array
For r = RowStart To RowEnd
    For c = colStart To colEnd
        sb.Add slbuildbindDat(FromGrid(r,c),"T") 'add bind string into stringbuilder
    Next
Next
slExeBind slBuildInsertOrUpdate("tblJournalEntry", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & _
"?" & $Nul & "?" & $Nul & "?"), _
slBuildBindDat(SQLiteDate(m_sDate), "T") & _
slBuildBindDat(m_sReference, "T") & _ 
slBuildBindDat(m_sNarration, "T") & _
sb.String,"V" + Format$(cols)
? "Record Saved!", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title

I might have to break table into two.

9
You've got Questions? We've got Answers! / Tricky Save Routine
Last post by Fredrick Ughimi - April 28, 2019, 05:15:54 pm
Hello,

I have a tricky situation trying to save values from a textbox and MLG on a FORM into a table at the same time.

On the FORM we have 3 values from Textboxes and 4 values from the MLG. On MLG there are two or three
rows to be saved at a time.

Has anyone done this? Any pointer would be appreciated.
10
General Board / Money no leading zero using CA...
Last post by cj - April 18, 2019, 09:39:27 pm 
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN AS LONG

 LOCAL sql,sArray() AS STRING

 slopen "junk.db3","C"

 slexe "drop table if exists t1"
 slexe "create table if not exists t1(c1 integer)"

 slexe "insert into t1 values(-1000),(-10),(-9),(-1),(0),(1),(9),(10),(100),(1000)"

 sql = "select rowid,"  +_
       " case" +_
       " when c1 > 0 then       ltrim(printf('%.2f',c1*.01),'0')"  +_ 
       " when c1 < 0 then '-'|| ltrim(printf('%.2f',c1*.01),'-0')" +_ 
       " else '.00'" +_
       " end  AS TheValues"+_
       " from t1"

 slSelAry sql,sArray(),"Q9"
 ? JOIN$(sArray(),$CR),,"No leading zero"

END FUNCTION
 
Pages1 2 3 ... 10