User Select what to report question

[Brewser]

Hello,

I am working on a database to show sports card grade value. I would like the user to select the year and the company from 2 dropdown value lists and hit a button to show the report. I want the report to be sorted by year and just have a field on the report for each card that states the company name.

I created 2 fields that would just hold the value the user selects, then I want to do a constrain found set on the year to find all cards in my database that match the year the user selected. Then I want the company name the user selected to be used to match the company criteria I have setup for card values.

My problem is I can't figure out what to use to do the search and how to set the field that matches the company.

I have tried contrain found set Cards::year==Cards::year_temp to just narrow down the year and set field Cards::company;Cards::company_temp. But this doesn't work.

I have the script to work just the way I want, but I have to enter the values manually. If I set the constrain found set Cards::year=="2001" and the set field cards::company;"XYZ" it works perfectly.

This is driving me crazy since I want the user to define the report and not have to duplicate the same scrip 100 times and put in the criteria.

Thanks for all your help on this.

[-Queue-]

Why not use a multiple-predicate relationship where globalYear = Year and globalCompany = Company? Once the globals are selected, you can test If [not IsEmpty(relationship::serial)] and Go to Related Records [show only related, relationship] to find the records for the report.

[Brewser]

Queue,

I may be a little confused. The database is already filled with all the years and all the cards for each year. The company is only 'XYZ' for all years in the database. If the user changes company from XYZ to ZXY the value of the card may change. This report is only to give the user a price guide based on year and company and not a list based on the company already inputed in the datbase.

Since the user has selected lets say 2004 and that is stored in a seperate field. I want it to search the database for all records that the year matches. I thought I could just do a constrain found set, but it does not want to match fields.

The company the user selects is going to change card price values for each card for the year they selected. I have the company stored in a temp field. I thought I could just use a set field, but again it doesn't want to accept field data from another field.

If I setup a seperate table and just call it year_reference and have one field called globalYear. I would then have to set the relationship from Year_Reference to Card_Data and link both globalYear and Year.

What would my script look like to pull only the matching year and do a set field for company to match my globalcompany?

I tried your go to related records, but it gave me a blank screen if I use the table year_reference. If I use the card_data table it still listed all the years.

Is there a sample file that someone has done that you are aware of that would be a good example of this. Where you can select the criteria for the report from dropdown fields?

Thanks again

[-Queue-]

I'm confused too. You seem to have contradicted yourself at least once, so I have no idea what you're trying to do or what data really exists. Did you try the multiple predicate relationship? Try creating a relationship first that only matches based on globalYear to Year. A portal using this relationship will only display records matching the globalYear. Adding the additional criteria, that globalCompany matches Company, allows only records matching the selected year and company to be displayed. So a Go to Related Records [show only related, thisrelationship] will give the records that you appear to want for your report.

We could clear up some of this confusion if you attached your file, by the way.

[Brewser]

Thanks Queue. My database is too big to attach. I will keep plugging away and if all else fails I will do a separate script for each criteria.

What I don't understand is why in both a constrain found set and a set field I can't have it take the data from another field. If I manually type in the definition it works great. Why can't it take the data from another field? Is there another command to do this?

[-Queue-]

It doesn't have to be the full database. Just one with a few records is good, so we can see the structure and how the relationships are set up...

Set Field can take the data from a global in Find Mode with no problems. If you take your user to a layout with the two global fields on it, pause, then Enter Find Mode and Set Field [field1, globalA] and Set Field [field2, globalB], then Perform Find, you'll have the found set you desire. However, it can be much quicker if you use a relationship with a simple Go to Related Records step.

Constrain Found Set should allow you to specify the requests. Simply select the field you want to match with the global and make your criteria for field to = globalX.

[Brewser]

Thanks, I finally got it to work right using your method. It only took me about 5 hours ;-)