Records not showing for preset range

[CR4Reid]

Hello,

Using CR2008 and I can't figure out why my records are acting the way they are. I need to show invoice references to a job. Each invoice book has invoice numbers which are their respective references. Each job will then be associated to a reference. Some invoices are voided or lost so any references that are missing will not have a job associated to it.

The reference field is a string field and jobs field is numerical. I'd like to enter a parameter of the invoice reference range (eg. 40000 to 40100) to show me which invoices were assigned to what jobs. If there are any missing ones, then the jobs will be blank.

I created the following formulas:

Report Header:
Whileprintingrecords;
numbervar Counter:={?Ref Start}-1;

Details Section:
Whileprintingrecords;
numbervar Counter:=Counter+1;

Great - I can show the range of the invoice references on my details section. I also added the parameters ?Ref Start and ?Ref End and filtered my records based on the invoice references converted to value in a formula called @value ref.

But as shown below it shows blanks after 40019 because there's no corresponding reference for 40020. I know there are other references after but it's not showing. I'm puzzled as to why this happens.

Am I approaching this problem the correct way? I'd love to hear some advise.

Thank you in advance!

Reid

MyCounter FR Ref
40000 40,000.00
40001 40,001.00
40002 40,002.00
40003 40,003.00
40004 40,004.00
40005 40,005.00
40006 40,006.00
40007 40,007.00
40008 40,008.00
40009 40,009.00
40010 40,010.00
40011 40,011.00
40012 40,012.00
40013 40,013.00
40014 40,014.00
40015 40,015.00
40016 40,016.00
40017 40,017.00
40018 40,018.00
40019 40,019.00
40020 0.00
40021 0.00
40022 0.00
40023 0.00
40024 0.00
40025 0.00
40026 0.00
40027 0.00

 

[lbass]

Is “FR Ref” the heading for {@value ref}? What is the content of that formula (and any nested formulas?).

It looks like you are hitting a null, so the first thing you might try is removing any null instances in the selection formula, as in:

Not isnull({table.field}) and//etc.

-LB

 

[CR4Reid]

Thanks for your reply LB. I should've clarified the following:

FR Ref is a check formula:
if {@MyCounter}={@value ref} then {@value ref} else 0

MyCounter formula:
Whileprintingrecords;
numbervar Counter:=Counter+1;

value ref formula: (This formula is to convert the Reference field to value)
val({InvoiceHeader.Reference})

You are correct that it can't find 40020 because it doesn't exist, but I'd like it to match against subsequent References that exists. Below is data including FR Ref field.

MyCounter FR Ref value ref
40,000.00 40,000.00 40000
40,001.00 40,001.00 40001
40,002.00 40,002.00 40002
40,003.00 40,003.00 40003
40,004.00 40,004.00 40004
40,005.00 40,005.00 40005
40,006.00 40,006.00 40006
40,007.00 40,007.00 40007
40,008.00 40,008.00 40008
40,009.00 40,009.00 40009
40,010.00 40,010.00 40010
40,011.00 40,011.00 40011
40,012.00 40,012.00 40012
40,013.00 40,013.00 40013
40,014.00 40,014.00 40014
40,015.00 40,015.00 40015
40,016.00 40,016.00 40016
40,017.00 40,017.00 40017
40,018.00 40,018.00 40018
40,019.00 40,019.00 40019
40,020.00 0.00 40021
40,021.00 0.00 40022
40,022.00 0.00 40023
40,023.00 0.00 40024
40,024.00 0.00 40025
40,025.00 0.00 40026
40,026.00 0.00 40027
40,027.00 0.00 40028
40,028.00 0.00 40029
40,029.00 0.00 40030
40,030.00 0.00 40032
40,031.00 0.00 40032
40,032.00 0.00 40033
40,033.00 0.00 40034
40,034.00 0.00 40035
40,035.00 0.00 40036
40,036.00 0.00 40037
40,037.00 0.00 40038
40,038.00 0.00 40039

I wish I can link MyCounter formula to my Reference field. That would solve my problem. Thanks again for your help.

CR4Reid

 

[lbass]

Is the issue that you want to show consecutive numbers even if a value ref is missing? Please answer this question.

You say the counter formula is just a check, but it looks like it is more than that—you actually want to show a row for even invalid/missing value refs. If this is the case, you could add a field to the report (even if suppressed) that is present in rows where there is no value ref. This would leave a blank in the value ref column so that your FR Ref formula could detect a match on subsequent rows. Right now your FR formula returns 0’s because your counter is oblivious to the fact that there is a gap in value refs and so the counter no longer matches after one instance of a gap.

If you don’t have a field that will force a gap for missing value refs, then another approach would be to create a main report that contains the counter formula. You would have to add a recurring field in the detail section to force it to count—it could be any field (even from an unrelated table) that will force more rows than the value ref field. Then insert a subreport that is linked on the counter formula and placed in the detail section. Any other fields that you want related to the value ref belong in the subreport. Remove the borders on the subreport.

-LB

 

[CR4Reid]

Hi LB,

Yes I do want to show consecutive numbers even if there's no corresponding value refs. This way I'll know which invoices that were not assigned to a job (due to voided invoice, lost/destroyed invoice, etc).

I think I'm going to try your subreport suggestion by having the counter in the Main report and have a subreport in the details section.

Thanks LB!

CR4Reid