Select Formula Question 1

[MikeVac]

I am looking for a way to select records from 1 table only when a specific value exists.
For example. Table 1 and Table 2 are joined. I want to bring back values A,B,C,D from Table 2 only when there is an A value.
When A exists then bring back A,B,C,D (if B,C or D exist) If there is no A then don't bring back anything.
Thanks.

 

[MikeVac]

Getting closer - right now it is just bringing back values = "A"
where is says {table1.ID}in your group selection should this be the field I am grouping on? - which is table2.col

 

[lbass]

You are trying to see if there is an A in some group which you show in your example as returning: 500, 501, 502, and I thought that was based on the {table1.ID} field. That is the field that belongs in the formula. You are testing for the presence of table2.col in that group. You could have an inner group on that (not sure why), but you have to have the initial higher order group on the table1.ID field.

-LB

 

[MikeVac]

Hopefully this will clarify - sorry for the confusion.

Table 1 and Table 2 are linked (1 to many) by an ID field.

I want to bring back records where a column in Table 2 called AssignedTo has at least 1 record = A.
If there is an A value then bring back all the records = A and startswith A-.

The report will be grouped by Table2.AssignedTo and inside that grouped by Table1.filenum.
The details will be Table2.AssignedDate, Table2.CompletedDate.

Group1 (Table2.AssignedTo)
Group2 (Table1.FileNum)
Details (Table2.AssignedDate, Table2.CompletedDate

Thanks

 

[lbass]

Please explain what field returns the 500, 501, etc.

In your example, you showed that the presence of A was being tested per the value of this field (which I will guess is the fileNum field, and that is the field which should be an outer group (Group #1), with the {table2.AssignedTo} group within that Group #2. Then the group selection formula should be:

sum({@hasA},{table1.filenum}) > 0 and
(
{table2.assignedto} = "A" or
{table2.assignedto} startswith "A-"
)

-LB

 

[MikeVac]

The 2 tables are joined by an Id # which is not used in the report. Table 1 has a value of 500 for example. In table 2 there is one or more records with a value of 500. For every ID # in table 1 there is at least one record in table 2 with that ID #.
The select must bring back records where A exists and then bring back the records = A or startswith A-
Thanks for your help

 

[lbass]

The question is: Is {Table1.ID} the field that returns 500? If not, what is the field that does this? This is the field you need to group on and then follow the earlier suggestion.

-LB

 

[MikeVac]

I have added an attachment with screen shots. Thanks

 

[MikeVac]

I added the attachment as a pdf

 

[lbass]

I'm sorry but I can't help you, since you are unable to answer my question.

-LB

 

[MikeVac]

Sorry, I thought I was by adding the attachment.
Thanks anyway