Query Sqlite and Headed List

[Graeme Harris]

Andy

Thank you for your prompt reply.

I followed your direction and was able to get it working using the Omnis 
Query class and adding a View to Sqlite DB  and then copy it into a 
Schema class.

The other option I was pursuing (which I preferred) was using a Table 
class with an SQL statement to retrieve the data this also worked, 
except that the field created in the list where date is stored was not 
able to be changed. In the Sql table it is stored as '2022-05-01 
00:00:00.00' and when it is loaded in the list it is changed to ' May 1 
2022 00:00:00.00'.
I tried your solution changing the #FD format before calling the table 
class and creating the list from SQL statement with no success it return 
the same as mention above. I also tried a For Loop referring to the date 
column in the list and changing it with a dat()  function, but no success.

I am thinking that a list created from SQL statement may be populated 
with all character fields which may be explains why I am not able to 
change data format but doesn't explain how the format changes from what 
is stored in the database and as it appears in the list field.

I hope this makes things a little clearer.

Graeme


On 5/15/22 5:56 am, TBS wrote:
> Graeme
>
> You can set #FD very simply like this :
>
> Calculate CurDateFormat as #FD
>
> (CurDateFormat is a character local variable - this is just so you can revert #FD to whatever it is at the start when we are done)
>
> Then do :
>
> Calculate #FD as 'D-m-y’ (or whatever format you want)
>
>
> Do you stuff, and then revert $FD
>
> Calculate #FD as CurDateFormat
>
> Although there are other ways of changing the column formatting of a headed list which may be more appropriate here - but that’s not what you asked !
>
> If I am reading it correctly what you need is two separate links to your location table from the trip table - correct ?
>
> To do this you can create a ‘view’ - which in simple terms makes a ‘copy’ access to a table under a different name so for example something like this sql :
>
> CREATE OR REPLACE VIEW locationlookup AS SELECT Location.Location_pk AS locationpkey, Location.LocationName as locationlookup FROM Location
>
> Would create a view which then allows you to use ‘locationlookup’ as any other table in constructing your query, so then use locationlookup as your second join (using the fields now called locationpkey and locationlookup instead of Location_pk and LocationName) and away you go ! (I am assuming you have a primary key column in your location table and are using foreign keys in your Trip table as your actual ‘join’ fields ?? If not the same principle exists just by using the LocationName field as your lookup - but using primary and foreign keys is definitely the way to go)
>
> (Note I am not clear how you are creating your query, you may need to add a schema for the view created above before you can actually use it in your setup !!)
>
> Does that help you ?
>
>
> Andy Hilton
> Totally Brilliant Software Inc
> Phone (US) : (863) 409 4870
> Phone (UK) : 0207 193 8582
> Web : www.totallybrilliant.com
> Helpdesk : http://totallybrilliant.kayako.com
> Email : andyh at totallybrilliant.com
> On May 15, 2022, 1:15 AM -0400, Graeme Harris <gfhwalkabout at fastmail.fm>, wrote:
>> I have to two part question that I am having trouble finding a answer to.
>>
>> I have a Trip table with columns StartDate, LocationFrom and LocationTo
>> plus other columns with a second table Location with a column
>> LocationName. I am building a Headed List with this data.
>>
>> Problem #1 If I build the the list from a table class using an SQL
>> statement everything works fine except I haven't found a way to change
>> the display format of the StartDate field in the Headed List. It seems
>> when the data comes into Omnis it is format by the #FD which I haven't
>> found how to change when importing.
>>
>> Problem #2 If I build the list using the Omnis Query class then the date
>> format can be set in the query class. I can set a join from LocationFrom
>> to LocationName but haven't figured out how to set the join from
>> LocationTo to LocationName. I can do it in an SQL statement but not in a
>> Query class.
>>
>> Any help with either problem would be greatly appreciated
>>
>> Graeme
>>
>>
>>
>> _____________________________________________________________
>> Manage your list subscriptions at https://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com