Question on filters

[lonestarfire]

I have made the set for the 2 tables and created the report but how do I make it ask for info in a certain field and then only bring up those files that match. Example On my work orders they need to come up by the month due so I want it to ask me which month and then only pull those months. In access (which is what we are using now but will be changing over to Alpha 5 if I can show it will do the hard stuff) in the code I have cri = "[fe] = [SERVICE SCHEDULE].[MONth] Or [fe] = [SERVICE SCHEDULE].[ANNUAL] "
cri = cri & "Or [fe] = [SERVICE SCHEDULE].[TWICE] Or [fe] = [SERVICE SCHEDULE].[three] Or "
cri = cri & "[fe] = [SERVICE SCHEDULE].[quarterly] Or [fe] = [SERVICE SCHEDULE].[six] Or "
cri = cri & "[fe] = [SERVICE SCHEDULE].[monthly] "
stDocName = "CUSTOMERS DUE REPORT"
Which tells it what field to compare then on the query to pull the data I have the following formula:[Enter Month in Number ie ##] In Alpha I got the report but I don't know how to get it to quesry for month due.

[Keith Hubert]

Re: Question on filters

Hi Ramona,

Welcome to Alpha.

There are several ways to do this and all very easy to do this in Alpha. I suggest you look at Dialog boxes in Action Scripting and also Ask variables.

Learn and understand how the function CMONTH() works.

[Stan Mathews]

Re: Question on filters

It might help someone help you, given that this is an Alpha message board, if you explain

cri = "[fe] = [SERVICE SCHEDULE].[MONth] Or [fe] = [SERVICE SCHEDULE].[ANNUAL] "
cri = cri & "Or [fe] = [SERVICE SCHEDULE].[TWICE] Or [fe] = [SERVICE SCHEDULE].[three] Or "
cri = cri & "[fe] = [SERVICE SCHEDULE].[quarterly] Or [fe] = [SERVICE SCHEDULE].[six] Or "
cri = cri & "[fe] = [SERVICE SCHEDULE].[monthly] "

what is the significance of(what is represented by)

[fe] is this a variable? a value?
[SERVICE SCHEDULE] is this the name of a table?
.[MONth] is this a field name?
what purpose do brackets [] fill?

In translating your sample you can replace & with +
you can replace OR with .OR.

cri = "[fe] = [SERVICE SCHEDULE].[MONth] Or [fe] = [SERVICE SCHEDULE].[ANNUAL] "

If the above guesses are correct in Alpha you would say

"month = "+quote(var->fe)+" .or. annual = "+quote(var->fe)

to test a field value in the month/annual field in the current table or parent table of a set.

[lonestarfire]

Re: Question on filters

It might help someone help you, given that this is an Alpha message board, if you explain



what is the significance of(what is represented by)

[fe] is this a variable? a value?
[SERVICE SCHEDULE] is this the name of a table?
.[MONth] is this a field name?
what purpose do brackets [] fill?

In translating your sample you can replace & with +
you can replace OR with .OR.



If the above guesses are correct in Alpha you would say

"month = "+quote(var->fe)+" .or. annual = "+quote(var->fe)

to test a field value in the month/annual field in the current table or parent table of a set.

[lonestarfire]

Re: Question on filters

It might help someone help you, given that this is an Alpha message board, if you explain



what is the significance of(what is represented by)

[fe] is this a variable? a value?
[SERVICE SCHEDULE] is this the name of a table?
.[MONth] is this a field name?
what purpose do brackets [] fill?

In translating your sample you can replace & with +
you can replace OR with .OR.



If the above guesses are correct in Alpha you would say

"month = "+quote(var->fe)+" .or. annual = "+quote(var->fe)

to test a field value in the month/annual field in the current table or parent table of a set.

The is the way the code is written in Access which is what I am trying to get Alpha to do. We need to change to Alpha. So I am trying to make the work orders as a form and get it to pull them up by the field (month due) In access we had to first tie the customer table to the service schedule table and then create a query that would ask us for the month due to open the report. But I think I understand but I want to be sure. Are you saying I first have to make a set where the service schedule it parent and the customer table child with the above code so it will query for that field?

[lonestarfire]

Re: Question on filters

Based on what I believe you were telling me I made this expression and added it to the set in the link box

''month = "+quote(var->fe)+" .or. annual = "+quote(var->fe)+" .or. twice = "+quote(var->fe)+" .or. three = "+quote(var->fe)+" .or. quarterly = "+quote(var->fe)+" .or. six = "+quote(var->fe)+" .or. monthly = "+quote(var->fe)

But it tells me that "Define link line 187 decompose link expression. Extra characters at the end of the expression.

What am I doing wrong?

[MikeC]

Re: Question on filters

oops..read incorrectly....

later edit: try s_quote() instead of quote() as it was designed specifically for quoting variables....

[lonestarfire]

Re: Question on filters

This is the expression I made based on what you said. I tried to create a set with the customer table as parent and service schedule a child with the following expression:

''month = "s_quote(var->fe)+" .or. annual = "s_quote(var->fe)+" .or. twice = "s_quote(var->fe)+" .or. three = "s_quote(var->fe)+" .or. quarterly = "s_quote(var->fe)+" .or. six = "s_quote(var->fe)+" .or. monthly = "s_quote(var->fe)

When I do that I get the following error:
Script UI_DLG_BOX: Define Link Line:187 Decompose_Link_Expression(VL) Extra Characters at end of Expression.

I don't know what I am doing wrong. Also for the gentleman that said I should learn CMonth() I can't find anything on that to look at.

Thank you all for the help

[Al Buchholz]

Re: Question on filters

Also for the gentleman that said I should learn CMonth() I can't find anything on that to look at.

A search of the wiki found this....

[MikeC]

Re: Question on filters

''month = "s_quote(var->fe)+" .or. annual = "s_quote(var->fe)+"  .or. twice = "s_quote(var->fe)+" .or. three = "s_quote(var->fe)+"  .or. quarterly = "s_quote(var->fe)+" .or. six =  "s_quote(var->fe)+" .or. monthly = "s_quote(var->fe)

would work better with all the plus signs added....

''month = "[SIZE=5][COLOR=Red]+[/COLOR][/SIZE]s_quote(var->fe)+" .or. annual = "[SIZE=5][COLOR=Red]+[/COLOR][/SIZE]s_quote(var->fe)+"  .or. twice = "[SIZE=5][COLOR=Red]+[/COLOR][/SIZE]s_quote(var->fe)+" .or. three = "[SIZE=5][COLOR=Red]+[/COLOR][/SIZE]s_quote(var->fe)+"  .or. quarterly = "[SIZE=5][COLOR=Red]+[/COLOR][/SIZE]s_quote(var->fe)+" .or. six =  "[SIZE=5][COLOR=Red]+[/COLOR][/SIZE]s_quote(var->fe)+" .or. monthly = "[SIZE=5][COLOR=Red]+[/COLOR][/SIZE]s_quote(var->fe)

[Keith Hubert]

Re: Question on filters

Hi Ramona,

Also for the gentleman that said I should learn CMonth() I can't find anything on that to look at.

Thank you calling me a "Gentleman". I hope that you have found CMONTH() now from Al's link.

I wanted to point you in that direction so that you could see the other Month functions and go from there.

[lonestarfire]

Re: Question on filters

Thank you all for trying to help me but I must be a complete idoit because I can't get this to work. So I am going to explain the whole thing over because maybe I am making this harder then it needs to be.
In Access we have a customer table with a field (fe) that tells us when that customer is due for service. This is so we can pull up work orders. The service schedule table was created because in the (fe) field we can enter 1 for Jan, or M for monthly, or 16 for jan and june, or 14710 for quarterly inspection and so on. When we open the report called work order it has a formula that asks us what month we are looking for. We enter 6 for june and then access goes to the service schedule table and compares all the entries in (fe) to what we are asking for and pulls up those records that match.
I imported the customer table in Alpha and the service schedule table. Now I am trying to create a set of them so that I can make my work orders. When I go to create the set I am linking the customer table (fe) to the service schedule table and in the link is where I am entering the info you have given me. But it just keeps telling me that it is invalid or incomplete. IS this the wrong spot and if so what do I link? And I am assuming that the Cmonth() is for it to ask me what month to look for is that correct? Is there an easier way to make all this work?
And Yes you are all gentleman because you are trying to help me figure out something that does not make sense to me. Thank you for all the help!!

[Keith Hubert]

Re: Question on filters

Hi Ramona,

And I am assuming that the Cmonth() is for it to ask me what month to look for is that correct

Sort of, there are several functions for Month that return different values.

As I said I was just trying to point you in a direction that would help you further.

Copy this into the Interactive window and play around with it.

x=date()
?x
= {03/06/2010}

?cmonth(x)
= "June"

? month(x)
= 6

Notice the difference between the two functions.

Hope this help a bit more.

It seems to me that the way the data is stored in the field is overly complicated. If the value in the field is 1 then that would be January. Alpha could get the info out of "14710" but would take a bit of code work. So we would tend to use separate fields, probably with check boxes and radio buttons.

[Keith Hubert]

Re: Question on filters

Ramona,

The other thing to look at are Dialog boxes.

how do I make it ask for info in a certain field and then only bring up those files that match

This would be done from a dialog box. The idea is that you get a value into a variable and then use that value as part of the filter.

So you create a dialog box with a list of all the months, the user makes a selection. The variable now has a value, lets say January. The value in the variable "x" could be either "January" or "1".

For your filter you will have var->x=CMONTH(Field_Name) if Field_Name is a Date. Or, if field_Name is a number then you could just use var->x=Field_Name in your filter expression.

As you will find out there are a number of ways to do things, but it all depends on your data and how easy it is to read.

[MoGrace]

Re: Question on filters

Based on what I believe you were telling me I made this expression and added it to the set in the link box

''month = "+quote(var->fe)+" .or. annual = "+quote(var->fe)+" .or. twice = "+quote(var->fe)+" .or. three = "+quote(var->fe)+" .or. quarterly = "+quote(var->fe)+" .or. six = "+quote(var->fe)+" .or. monthly = "+quote(var->fe)''

But it tells me that "Define link line 187 decompose link expression. Extra characters at the end of the expression.

What am I doing wrong?

If this entire string is to be your filter then it must be enclosed in " ". But then you have to escape the other quote characters by using the \character first. I just know Mike C has a link handy for that article!