Complex query user input

[Rem]

(Well, maybe not that complex but I had to come up with a good subject)

I want users to be able to search using different criterias. Say from date to date. Or this week or last week or five weeks ago or whatever.

So, the only way I can think of is to create new fields in my BO and use a customform or maybe a new BO, just for searching with several customforms.

So the question is: is this the way to do it or is there some other way that is more, "the right way"?

/Rem

[tford]

Rem,

There are a number of different ways to do very complex searches. If you can provide more specifc details, it would be easier to help you reach your goal.

Otherwise, as my friend says, "it's like trying to send a haircut in the mail".

Tom

[Rem]

Ok, lets start with something that sounds simple at first glance (and maybe is?):

I want to have a dropdown where I can choose from week numbers. My thought was to have CURRENT_WEEK and then CURRENT_WEEK-1, CURRENT_WEEK-2 and so on for like 5 weeks back or so. I want to run this query on an attribute which is a date and is called job.date.

A simpler way to do this might be to enter the weeknumber manually, but then you would have to know which week it is right now (and you don´t allways know that)

The entry must be made from a form (not the "Ask at runtime function"), but then I have another question, how do you connect a filteringrule to a form? If you tick the box "Use form" in a query, the rulepart disappears.

/Rem

[tford]

Rem,

In the BO you are trying to search, you have job.date. Do you also have an attribute in job BO for the week number of the job?

Tom

[Rem]

No, do I need one for the query?

/Rem

[tford]

Yes -- for example have an attribute Job.JobWeek & have it be calculated. Have a rule that sets it like: Job.JobWeek=WEEK(Job.JobDate).

That will give you something easy to search against.

Tom

[tford]

In general, anything that you want to search against or display, you will need to define as a BO attribute -- either directly in a particular BO or by shortcut.

Tom

[tford]

Rem,

The week # drop down search with current week #, etc will be a bit more advanced. I suggest you get a basic seach down first, then add to your understanding.

I will send you a sample bsv. If anyone else need it, send me a PM.

Tom

[tford]

Rem,

I'm wondering if you might want to include an attribute Jobs.WeeksAgo that would indicate how many weeks ago the job was.

Would that be a helpful way to search?

Tom

[tford]

Rem,

One problem you will have with searching on week number is that Job instances from any year will show up. For example if you search on week 5, you might get instances from 2008, 2007, 2006, etc.

Tom

[Rem]

Well, the years won´t be a problem for another year, but it wouldn´t be that hard to filter it away with a YEAR function like CURRENT_YEAR, right?

Regarding Jobs.WeeksAgo, do you mean that this field should be automatically calculated by a process or something and then used for the query or?

/Rem

[tford]

Rem,

Re: years
Personally, I like to build apps that take things like this into consideration before they are needed so I'm not mopping up in another year.

Re: Jobs.WeeksAgo - it seems like you were saying it might be more intuitive for people to search for how long ago a job was & that week #'s are something that people really remember.

You could have Jobs.WeeksAgo calculate by rule & before a search run an UPDATE action to ensure that the WeeksAgo value is current. Your search form could then have a drop down list of choices for Weeks Ago: 0,1,2,3,etc. The search would then find records that match that # of weeks ago.

Tom