Using omit record runs out of records in loop.

[Brewser]

Hello, I am working on a DB for card collectors. I have a script that will do a report layout for all cards the person owns or has put in at least a 1 for quantity by a perticular company. The search will look for >0 in QTY and company. I do have this setup with a self-join relationship to match year, so it does find the right year.

I have the user select the company and year. The report will go through and find all cards with >0 and company that equals the global company they selected.

The problem is it works great if they have cards by the year and company, but if they have zero then my script just keeps looping. I need to figure out how to show a dialog box when it comes up with zero for records found.

Here is what I have so far that works if they match criteria:

Constrain Found Set (Card::Company = Card::PriceGuideCompany) - price quide company is a gobal

Sort records (year and company)

Go to records (first)

Loop

If Card::Quantity <1

Omit record

Else

Go to Record (next;exit after last)

end if

end loop

Go to Record (first)

Loop

If Card::Company not= Card::PriceGuideCompany

Omit record

Else

Go to Record (next;exit after last)

end if

end loop

Again, this above works great if it has at least one value that matches the above criteria. If not it goes into a loopy funk and I have to abort.

I have tried several things, but nothing seems to capture the no records found.

I would like to then have a dialog box that comes up and says

"You have no cards that match company and year, please try again."

Thanks for all your help. You guys (and gals) have been a great find.

[John Glenn]

Maybe I missed something, but it seems to me you should just be doing

Constrain Found Set( (Card::Company = Card::PriceGuideCompany) AND (Card::Quantity < 1)) and skip the loops altogether. (in other words, two requests in the same find)

That's what a find is good at, excluding records that don't match the criteria you have picked. Loops are slower.

If there is some good reason why you need the loop, you could always say

if (Get(FoundCount) <1 )

Show Custom Dialog, etc.

[Brewser]

John, I tried using just the constrain found set before, but it will not omit the quantity <1. It lists all the cards regardless of quantity or company. That is why I turned to the loop and it worked great except when it didn't find a match and then it just shows my blank report with the hour glass.

I tried your if get found count, but it just did the same thing. Maybe there is something wrong with the way I have my relationship, but it works for a report I did where the user can pick the company and year from a list and it will give them every card from the list regardless if they have any or not.

I will keep trying different scenerio's.

Thanks for your help.

[-Queue-]

Why not add a field with a calculated value (or global value) of zero, then create a relationship based on PriceGuideCompany = Company and globalzero < Quantity?

[Brewser]

John, Another reason I am forcing the loops is my database is only populated with one company and for the price guides I want the user to pick from a list of other companies and it will price the cards accordingly, by listing the cards and replacing all the pre-defined companies with the one the user selects.

For the custom price guides such as cards I own for X year and by x company or cards I am missing from x year and x company.

The user can duplicate any card and then change the company for that card.

So my thought process through this is first gather all the cards for a certain year. Then go through and omit all the cards that have no quantity. Then omit cards that don't match companies. This will then populate the report with cards the person owns that match their criteria. If after omitting records that don't match I want a message that pops up and tells them they don't have any of the cards.

I haven't got to the missing list yet, but I imagine it is only a reversal of the quantity.

I don't know why the contrain found set doesn't work, because it still just populates everything even if their is zero quantity and regardless of company. The looping works though except when it runs out of files.

The only thing I have before the constrain found set or the loops is:

allow user abort (off)

set error capture (on) even though I am not using it, but have been trying different things

Go to related record (price Guide and using layout 'price guide report') this is a self-join to my card main table connecting year in price guide to PriceGuideYear in Card.

Sort by year & card # & company

Maybe it is some type of the way things are ordered.

Thanks again for all your help and if you can add another option to this that would be great.

[Brewser]

Queue, I don't quite follow. What type of relationship? Are you saying add a field that has a value of zero and in the constrain found set do card::company=card:PriceGuideCompay and card::globalZero < card::quantity ?

So that would match company to the company the user selects and the card quantity has to be 1 or more.

Then what if the found set turns up a zero with no matches? Would I do a:

IF Get(lasterror) <>0

Show Custom Dialog ""

or is there some other way to display no found matches.

Also do you think this would work in reverse if I do a missing price sheet, were I could create a globalone with 1 hard coded and with globalone > Quantity?

Thanks again, you have been a great help. This is the last thing I need to get accomplished and then it's ready.

[jfmcel]

Just throw an Exit Loop If ["Status ( CurrentFoundCount ) < 1"] step into your loop...

[-Queue-]

No, I mean a multiple predicate relationship. First you match your company fields with an =, then match the global or calculation to your quantity field with a <. If you Go to Related Record [show, thisrelationship] and your global company field has a value, you will get all the records that meet the criteria. You can test if there are any valid records before doing the GtRR with If [not IsEmpty(thisrelationship::serial)]. To do the inverse, you could use the calculation to qty with an = (if the quantity would be zero) for the second match criteria.

[-Queue-]

Yes, you could use your loop and Exit Loop If [not Get(FoundCount)], but a relationship would be much faster, if you're concerned about efficiency.

[Brewser]

Thanks, The exit loop works great, but how do I put in a custom dialog to show why I exited.

[-Queue-]

If [not Get(FoundCount)]

Show Custom Dialog

Exit Loop If [1]

End If