Hello,

I have have been looking at the demo version of your product to sync calendar items from a dBase (.dbf) database to Outlook's calendar. The problem is, the database stores the appointment start date in one field (date/time), and the start time in another field (varchar).

Is there a way to combine these two fields and link them to the Outlook appointment start field?

Thanks,

James

a VIEW is the solution for your problem,
I'm not an expert in dbase, but I think they do not support VIEW's?

I am not that familiar with dbase either, but I believe you are correct. I think I have gotten around this, however, by linking the table to an Access database, and then creating a query which joins the two fields.

I am encountering a new problem, however, when trying to convert this new field (varchar) to the Outlook Start field (datetime).

I am storing the date and time in this format: mm/dd/yyyy hh:mmAMPM.

I am using the following conversion string: %m/%d/%Y %I:%M%p, but I still get the error "Date must be converted from/to a string."


This is what I am trying to do, convert it from a string, to a datetime, using the appropriate format string.

Thanks again for your help,

James

Hi,

you are correct, this is a bug.

Please see latest Beta for a hot fix:
http://support.geniusconnect.com/Posts/t222- 

Why are you converting from date to varchar and then again to date, is it not possible to convert it in access?

It is possible, I was using the wrong function. I have now converted to datetime in access, and this syncs with Outlook properly.

I want items created in Outlook to transfer back to the database table, which stores the date and time separately.

Is there a way to separate the time from the date in the Outlook start field in order to write back to the .dbf table?

This looks to be a very useful product, and your support so far has been extremely well.

Thanks again!

Hi,

to split values back to database you can use instead of triggers on a VIEW, but these are not possible with MS Access.
Other option is to write your own SQL Statements for Insert/update/Delete, see Assign Table->Options->SQL Commands.

I am having trouble coming up with the correct SQL statement...

I would like to take the Outlook start time (@APPT_START), and insert the date into a field called APPT_DATE, and the time into a field called APPT_TIME.

All other fields can be populated by the corresponding Outlook fields.

Hi,

we don't have params like @APPT_START or APPT_TIME!

Perhaps I spoke incorrectly. I am synching items in the database (.dbf) table (appt) through an Access query (q2Appt), which has the following fields:
APPT_NAME, APPT_TYPE, APPT_DUR, APPT_START.
These fields synch with the following Outlook fields, respectively:
Subject, Location, Duration, Start

The query field, APPT_START, combines two other fields from the query, APPT_DATE, and APPT_TIME.

I am trying to synch new Outlook Calendar items back to the Access query (q2Appt) but I recieve the following error:

23000 Cannot update 'APPT_START'; field not updateable.

This makes sense, as this field is simply a view of the data in APPT_DATE, and APPT_TIME.

So what SQL statement would I use to seperate the date from the time stored in the Outlook Start field, in order to write the date back to the APPT_DATE field, and the time back to the APPT_TIME field.

Thanks.

Hi,

I'm not sure if it will work, but you can try SQL statement like this:

UPDATE <your tabel !not the VIEW!> SET APPT_DATE={fn LEFT( '@APPT_START param' ,12) },
APPT_TIME={fn RIGHT( '@APPT_START param' ,8) } .....other columns.. WHERE <your prim. key>= '@prim key param'

This will split (ODBC function syntax)the datetime to date string and time string, but i'm not sure if it will work.

Hi,

I have recreated your scenario and it is works:

MS Access query (VIEW) to link with GeniusConnnect:SELECT Appointments.APPT_ID, Appointments.APPT_NAME, CDate(Format([Appointments].[APPT_START_DATE],"yyyy-mm-dd")+Format([Appointments].[APPT_START_TIME]," hh:nn:ss")) AS Start, Appointments.APPT_DUR
FROM Appointments;

SQL statement for new prim. keys:
select Iif(max(APPT_ID) is null, 0,max(APPT_ID) + 1) from Appointments

(You may change also the param for APPT_START_DATE to somethink like {fn LEFT('@Start' ,10) } to remove the time part)

InsertSQLCmd:
INSERT INTO `Appointments` (`APPT_ID`,`APPT_START_DATE`,`APPT_START_TIME`,`APPT_DUR`,`APPT_NAME`) VALUES
('@APPT_ID','@Start',{fn RIGHT('@Start' ,8) },'@APPT_DUR','@APPT_NAME')

UpdateSQLCmd
UPDATE `Appointments` SET `APPT_NAME`='@APPT_NAME',
`APPT_START_DATE`='@Start',
`APPT_START_TIME`={fn RIGHT('@Start' ,8) },
`APPT_DUR`='@APPT_DUR' WHERE `APPT_ID`='@APPT_ID'


DeleteSQLCmd:
DELETE from `Appointments` WHERE `APPT_ID`='@APPT_ID'

Please see example (.zip file) with MS Access db and .ini file. (Use Import button on Assign Table Dialog):
http://www.geniusconnect.com/d/MSAccessSplit.zip 

This has definately gotten me on the right track...

The only problem I have now, is that my APPT_START_TIME field is a varchar(6), not a date/time field.

This results in '00:00' as the value when I use {fn RIGHT('@Start',8)} in the INSERT statement. Is there a conversion/format commaand I can use here to format the time as hh:mm[AM/PM]?

I must say your support and product thus far have been outstanding!

Automatic-synchronization is available in the full version, correct?

Also, if I purchase a single-user license now, can I upgrade to a multi-user license later?

Thanks again for your help.

Also,

I have another field, CLIENTNUM, I would like to populate by cross-referencing the '@APPT_NAME' value against another table. Is this possible?

Something like this:
SELECT CLIENTNUM FROM Client WHERE CLIENT_NAME = '@APPT_NAME'

And then have this value populate the CLIENTNUM field.

Thanks again.

"This results in '00:00' as the value when I use {fn RIGHT('@Start',8)} in the INSERT statement. Is there a conversion/format commaand I can use here to format the time as hh:mm[AM/PM]?"
The only other MS Access function I found is Format( '@Start','hh:nn'), this will convert to only hour and minute, but I have no idea how to format the AM/PM.


"Automatic-synchronization is available in the full version, correct? "
Yes, it is availible from Business license and higher.

"Also, if I purchase a single-user license now, can I upgrade to a multi-user license later?"
Yes.

"I have another field, CLIENTNUM, I would like to populate by cross-referencing the '@APPT_NAME' value against another table. Is this possible?"
You must join this table in your VIEW (query). Example
select t1..., t2.CLIENTNUM from <Your Table> t1, Client t2 where t2.CLIENT_NAME = t2.APPT_NAME
or using subquery:
select t1..., (select t2.CLIENTNUM from Client t2 where t2.CLIENT_NAME = t2.APPT_NAME) from <Your Table> t1

You can also try to install the latest Beta
http://support.geniusconnect.com/Posts/t222-  and use your original conversion: %m/%d/%Y %I:%M%p (so converting in GeniusConnect and not in DB).

You will have to change your query column APPT_START back to a varchar in your original format:mm/dd/yyyy hh:mmAMPM
and use the {fn LEFT('@Start' ,xxxx) } function in the insert/update statement.