When I sync data back from Outlook to my VFP odbc table (dbf) I get the standard fields, but when I try to perform a function conversion I have problems.

I need to extract the Date and the Time as separate fields. I see similar post in the dBase forum and have tried but so far no luck.

I am using
Time_AmPm='@StartTime' - but since Time_AmPm is Char I get a literal @Starttime
Time_AmPm={fn RIGHT('@StartTime',8)} I still get a portion of the literal: arttime
Time_AmPm={fn RIGHT(@StartTime,8)} without the quotes produces an error.

I have disabled the ANSI conversion in the assign table, but have used it with it check and don't see any difference.

is the {fn <functionname>} syntax correct?
Do I call FoxPro functions or SQL functions?
Is the @StartTime supposed to be surrounded by quotes?
Is the variable @StartTime a Char type or a DT?

Thank you!

Hello,

you can use Foxpro SQL syntax or ODBC SQL syntax.


{fn XXX} is ODBC syntax for calling functions and and can be used only with ODBC functions.


Supported ODBC function(s) depends on foxpro ODBC driver implementation and version.

"@StartTime supposed to be surrounded by quotes?"
Yes, it must be surrounded by quotes.
'@StartTime' is a Char type




Please see ODBC functions:
http://msdn.microsoft.co...windows/desktop/ms714639 (v=vs.85).aspx

{fn RIGHT('@StartTime',8)} is correct ODBC syntax to extract last 8 characters as string.


you can also try function CONVERT (if supported by your ODBC driver):

http://msdn.microsoft.co...windows/desktop/ms715381 (v=vs.85).aspx

Thank you so much for your help.

You said: {fn RIGHT('@StartTime',8)} is correct ODBC syntax to extract last 8 characters as string.

When I do it this I get the literal: tartTime stored in my db field - rather than the stored value inside the '@StartTime' -

I have tried every way I can thing of to get this perform the function on the variable, but so far no luck.

Hello,

where are you placing the function? is StartTime real column in your table and linked to an Outlook column?

Format Parameter: ‘@<Col. Name>’


Example:

Column: tbl_zipcode

Parameter: ‘@tbl_zipcode’

I have a StartTime field in my db and that is getting updated properly. I am trying to set a field named Time_AmPm in my db using
Set Time_AmPm={fn Right('@StartTime',5)}

The Time_AmPm field ends up with a portion of the string, i.e.: tarttime. If I do a plain Set Time_AmPm='@StartTime' I get a portion of the date in the StartTime, i.e. 04/11/20. (the field is 8 characters long, so it puts the first 8 into the field)

I am using the SQL commands section of Genius Connect to "UPDATE Set". Because of some weirdness I am using the UPDATE command in both the Insert and the Update sections of SQL Commands. This is working on fields that do NOT require a function conversion.

what is the syntax of whole update query?

Insert:
update cli_event_cal set starttime='@starttime',
modified_d='@modified_d',
date='@starttime',
endtime='@endtime',
time_ampm='@starttime'
where pk = '@pk'

Update:
update cli_event_cal set starttime='@starttime',
modified_d='@modified_d',
date='@starttime',
endtime='@endtime',
time_ampm='@starttime'
where pk = '@pk'

These two above work, but I get the actual date from datetime in my Time_ampm field. I have tried
Time_AmPm={RIGHT('@starttime',8)} but that is when it puts the literal: tarttime into my time_ampm field.

time_ampm= {fn RIGHT('@starttime',8)} should work. What is your Outlook and GeniusConnect version?

It is GeniusConnect 5.0.1.2 and Outlook 2003 sp3 - The Outlook was without the SP3 but I applied the sp3 and tried it again before writing this. The SP did not help this issue.

The VFP ODBC can be a bit quirky, but it is frustrating that I can get the plain data but not apply any sort of function. I might have to handle this from the db/application side.

I have been trying different combinations of quotes and brackets but so far no luck.

All in all GREAT help from you. Great product - Thank you very much!

Are you using GeniusConnect 5.0.1.2 unicode?

you don't have to use different brackets, if the starttime='@starttime' gets replaced correctly with runtime value, all other occurences of '@starttime' should be replaced (it is one function replacing column parameter with runtime value), the problem will be somewhere else

Yes - it is the unicode -
Version:501201 Unicode 32 bit build.
Edition:5, Outlook version support: 2000 or higher.
Microsoft® Office Outlook® 2003 (11.0.0.8169)

Thanks for the tips on the brackets, I have tried different functions, convert, cast, substr, substring, left, right, I can't get anything to pull the actual data so far.

Hello,

please mail us ( info at geniusconnect.com):
-your databaase (zipped) with only table used in mapping
-mappings (Assign Table dialog->Export button)

Thanks for you help - I emailed this 12 April 2013