B4J Question 3+ questions about dates in B4XTables from Excel spreadsheets

[Tim Chapman]

1. How do I avoid the cells being populated with 12/31/1969 due to the cells being blank in the spreadsheet.

2. On line 56, I Dimmed the DueDate as a string. This does not seem right to me, but if I try as a date, it is a syntax error.

3. How do I manipulate dates? What can I do with dates? How do I compare dates? How do I see how many days it is until a date? How do I get today's date?

Thank you!

 

[Mahares]

How do I avoid the cells being populated with 12/31/1969 due to the cells being blank in the spreadsheet

For your project to work properly, you have to set the dates as TEXT not DATE since you have mostly blank dates. The in memory database used by B4XTable expects ticks of those dates and converts the dates to ticks internally and display them as real dates on the B4XTable. If you set the dates as DATE then, the code crashes. Here is the complete corrected code for the sub Private Sub B4XPage_Created:

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    XL.Initialize
    Dim result As XLReaderResult = XL.Reader.ReadSheetByName(File.DirAssets, "Todo List.xlsx", "Sheet1") 'complete sheet   
'    result.LogResult(True)
    B4XTable1.AddColumn(result.Get(XL.AddressName("B1")), B4XTable1.COLUMN_TYPE_TEXT) 'Task
    B4XTable1.AddColumn(result.Get(XL.AddressName("C1")), B4XTable1.COLUMN_TYPE_NUMBERS) 'Status#
    B4XTable1.AddColumn(result.Get(XL.AddressName("D1")), B4XTable1.COLUMN_TYPE_TEXT) 'Status
    B4XTable1.AddColumn(result.Get(XL.AddressName("E1")), B4XTable1.COLUMN_TYPE_TEXT) 'Context
    B4XTable1.AddColumn(result.Get(XL.AddressName("F1")), B4XTable1.COLUMN_TYPE_NUMBERS) 'Context#
    B4XTable1.AddColumn(result.Get(XL.AddressName("H1")), B4XTable1.COLUMN_TYPE_NUMBERS) 'Category#
    B4XTable1.AddColumn(result.Get(XL.AddressName("I1")), B4XTable1.COLUMN_TYPE_TEXT) 'Category
    B4XTable1.AddColumn(result.Get(XL.AddressName("J1")), B4XTable1.COLUMN_TYPE_NUMBERS) 'Task Priority
    B4XTable1.AddColumn(result.Get(XL.AddressName("K1")), B4XTable1.COLUMN_TYPE_NUMBERS) 'Aggregate Priority
    B4XTable1.AddColumn(result.Get(XL.AddressName("M1")), B4XTable1.COLUMN_TYPE_TEXT) 'Project
    B4XTable1.AddColumn(result.Get(XL.AddressName("P1")), B4XTable1.COLUMN_TYPE_TEXT) 'Due Date
    B4XTable1.AddColumn(result.Get(XL.AddressName("Q1")), B4XTable1.COLUMN_TYPE_TEXT) 'Reminder Date
    B4XTable1.AddColumn(result.Get(XL.AddressName("R1")), B4XTable1.COLUMN_TYPE_TEXT) 'Done Date
   
    Dim TableData As List
    TableData.Initialize
    For Row1Based = 2 To result.BottomRight.Row0Based + 1
        Dim Task As String = result.Get(XL.AddressOne("B", Row1Based))
        Dim StatusNum As Int = result.Get(XL.AddressOne("C", Row1Based))
        Dim Status As String = result.Get(XL.AddressOne("D", Row1Based))
        Dim Context As String = result.Get(XL.AddressOne("E", Row1Based))
        Dim ContextNum As Int = result.Get(XL.AddressOne("F", Row1Based))
        Dim CategoryNum As Int = result.Get(XL.AddressOne("H", Row1Based))
        Dim Category As String = result.Get(XL.AddressOne("I", Row1Based))
        Dim TaskPriority As Int = result.Get(XL.AddressOne("J", Row1Based))
        Dim AggregatePriority As Int = result.Get(XL.AddressOne("K", Row1Based))
        Dim Project As String = result.Get(XL.AddressOne("M", Row1Based))       
        Dim DueDate As String = result.Get(XL.AddressOne("P", Row1Based))
       
        If DueDate <> "" Then
            DueDate = DateTime.Date(DueDate)
        End If
       
        Dim ReminderDate As String = result.Get(XL.AddressOne("Q", Row1Based))
        If ReminderDate <> "" Then
            ReminderDate = DateTime.Date(ReminderDate)
        End If       
       
        Dim DoneDate As String = result.Get(XL.AddressOne("R", Row1Based))
        If DoneDate <> "" Then
            DoneDate = DateTime.Date(DoneDate)
        End If
       
        TableData.Add(Array(Task, StatusNum, Status, Context, ContextNum, CategoryNum, Category, TaskPriority, AggregatePriority, Project, DueDate, ReminderDate, DoneDate))
    Next
    B4XTable1.SetData(TableData)   
End Sub

The above answers your questions 1 and 2. As for question 3, use DateUtils library to manipulate the string dates. But remember, you have to parse string dates to ticks so the in memory database accepts them before they dispaly on B4XTable as string dates.. If you encounter propblems, post and someone in the forum will help you.

ADDED THIS FOR MORE CLARITY TO YOUR ITEM 3 REGARDING MANIPULATING DATES:
FOr instance if we want to calculate the number of days between the due date and reminder date and display on the B4XTable, here is what we do:
1. Add a column to the table:

B4XTable1.AddColumn("DueDateMinusRemDate",B4XTable1.COLUMN_TYPE_NUMBERS)

2. Add this below code:

Dim DueDateMinusRemDate As Int
        If DueDate <> "" And ReminderDate <> "" Then  'you need the JDateUtils lib checked
            Dim p As Period = DateUtils.PeriodBetweenInDays(DateTime.DateParse(ReminderDate),DateTime.DateParse(DueDate))
            DueDateMinusRemDate = p.Days
        End If 
        'Note: below line now has 1 more element added to the arrray at the end of it:        
        TableData.Add(Array(Task, StatusNum, Status, Context, ContextNum, CategoryNum, Category, TaskPriority, AggregatePriority, Project, DueDate, ReminderDate, DoneDate, DueDateMinusRemDate))

 

[Tim Chapman]

Thank you Mahares!

 

[Mahares]

Thank you Mahares!

I would rather see a post that disputes , confirms ,follow up or gives a like' if satisfied, rather then creating a post that only says: 'Thank you' and that is all. It does not benefit because members read it only to see a thank you when expecting a full answer. You cannot tell from a post that has only 'Thank you' whether the person used the answer or not, took a different route, or was reluctant to confront it I really appreciate a Thank you when handed to me, but for the sake of forum efficiency and the fewer number of posts per thread, there are better ways.