IAmTheWolf
2013-04-10T17:52:00Z

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!
Sponsor
GeniusConnect documentation search (User Manual Downloads...)
Administrator
2013-04-11T12:50:00Z
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
IAmTheWolf
2013-04-11T14:50:00Z
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.

Administrator
2013-04-11T15:20:00Z
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’



IAmTheWolf
2013-04-11T15:38:00Z
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.

Administrator
2013-04-11T15:57:00Z
what is the syntax of whole update query?
IAmTheWolf
2013-04-11T17:24:00Z

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.


Administrator
2013-04-11T18:50:00Z
time_ampm= {fn RIGHT('@starttime',8)} should work. What is your Outlook and GeniusConnect version?
IAmTheWolf
2013-04-11T22:02:00Z
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!


Administrator
2013-04-11T23:22:00Z
Are you using GeniusConnect 5.0.1.2 unicode?
Administrator
2013-04-11T23:39:00Z
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
IAmTheWolf
2013-04-12T11:08:00Z
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.
Administrator
2013-04-12T17:02:00Z
Hello,

please mail us ( info at geniusconnect.com):
-your databaase (zipped) with only table used in mapping
-mappings (Assign Table dialog->Export button)
IAmTheWolf
2013-04-15T13:40:00Z
Thanks for you help - I emailed this 12 April 2013