QXpress Community

Hello,

I am trying to use the sql override function for a table column. What I want is to show the Last completed service call for each customer.

As an interrum step, I am trying to  get any sql function to work so I started off with the autogen code from the ui. Right now, when I use this code, the table does not display on the screen. If I delete the column the table works fine.

SELECT Max(ActivitySequence) FROM viewActivity WHERE viewActivity.Entity = viewaccCustomer.FullName  OR viewActivity.Entity = viewaccCustomer.RecordID

Questions:

1) why does the generator use "viewActivity" instead of the table name of "Activity" ?

2) is "viewaccCustomer" the name of the current record in the table? Is it tied to the name of the ui table or the database table?

3) In the reports webinar it shows that the link between the Customer Table and the Activity Table is via the Activity.Entity = Customer.RecordID relationship. Is it not the same here?

4) are the database functions Min and Max available in the ui column sql override function?

5) To get what I really want would I use:

SELECT Max(ActivityStartDate) FROM Activity WHERE Activity.Entity =  viewaccCustomer.RecordID AND Activity.Status = "Completed"

Thank You.

NOTE - if you are a regular QXO user, and not a hardcore programmer QXO user, don't even bother reading this post. It'll scare you!

Stephen,

I was able to get what you wanted by putting this into a Customer grid, overriding the JobStartDate field:

SELECT Max(DueDateStart) FROM viewActivity WHERE viewActivity.Entity = viewaccCustomer.FullName  AND viewActivity.ActivityStatus = 'Completed' AND viewActivity.ActivityType = 'Work Order'

Only problem is that it is delivering the times in UTC time, which for me in Eastern time was 5 hours off.  Since it was a date column that I was overriding I was hoping that it would still convert to local time.  I'm going to put a ticket in here to see if we can get that done.

1) why does the generator use "viewActivity" instead of the table name of "Activity" ?

-You are getting a peep into the inner layers that make up Method's architecture here.  What you see in QXO is a view, not the actual raw table data.  Otherwise you would see integers everywhere there is a dropdown instead of the joined table value.  So in real life data is stored in Activity but shown to you via viewActivity.

2) is "viewaccCustomer" the name of the current record in the table? Is it tied to the name of the ui table or the database table?

-viewaccCustomer is a filtered view of the the accEntity table, which contains customers, vendors and employees.
-If you are doing a grid based on customers, the "= viewaccCustomer.FullName " portion of the override will be able to link the current record in the grid to override query.

3) In the reports webinar it shows that the link between the Customer Table and the Activity Table is via the Activity.Entity = Customer.RecordID relationship. Is it not the same here?

-The webinar is the user friendly version of what you get a peep into with SQL overrides. 

4) are the database functions Min and Max available in the ui column sql override function?

-Yes, just choose SUM and then type over top of it with Min and Max.

5) As shown above.

Mark,

It worked!!!

Actually the response was invigorating not scarry. Hold my had programming is scarry! You are always trapped and limited by some project manager's assumptions. In my experience "User Friendly" means programmer (me) maddening.

Please get the programmers to fix the formatting though. it does come through as date and time (wrong time at that) despite saying that I want date only.

Is there a way in the report generator to filter the activity records to be ActivityType='Work Order' ?

Stephen,

We have created a ticket to address the formatting.  Might take a while for that to come in though, as we have a pretty jam packed production schedule.

As for the report, yes, you should be able to filter.  Best thing to do is create a new forum post, though, so that we can mark this one as "Answered".  That way someone looking through the forum for SQL Override won't get confused that it turned into a topic on report designing!!

Paul