Thank you Char. I have built many reports in Report Designer, so I'm very familiar with adding fields and creating formulas. However, I'm asking specifically about adding fields from the Contracts application into a design.

One field (Last Auto Number) from a contract I'm trying to add to a report brings up an error message stating that due to the current sort order, Report Designer cannot directly access the "CN - Contract" record, and it suggests using the LOOKUP function.

My specific question is, has anyone used the LOOKUP function formula to add a field from the Contracts application, and if so, do you have an example of the formula?

Thank you Char. I have built many reports in Report Designer, so I'm very familiar with adding fields and creating formulas. However, I'm asking specifically about adding fields from the Contracts application into a design.

One field (Last Auto Number) from a contract I'm trying to add to a report brings up an error message stating that due to the current sort order, Report Designer cannot directly access the "CN - Contract" record, and it suggests using the LOOKUP function.

My specific question is, has anyone used the LOOKUP function formula to add a field from the Contracts application, and if so, do you have an example of the formula?

Hi - 

Please open your design in Report Designer, navigate to Design > Sort Order, and send a screenshot. This will assist in creating a formula example.

Lookup formulas rely on the driving records of the design they are being added to, so having that information would be very helpful!

In the Sort Order window, the Process All box is set to "JC - Jobs", and the sort order is set to "Print in job order".

Thank you Char!

An example of a lookup formula using the job and contract records, which requires the job ID and contract ID to be identical, including any dashes or dots, is:

LOOKUP(last auto number (CN Contract), Job (JC Job))

Additionally, you can retrieve the last draw number directly from the job record. This field, called "Last Draw," is updated with the highest draw number posted in job cost. The draw number on the contract can become out of sync with the job's draw numbers, as the contract last auto number updates each time an invoice is final printed. For instance, if draw 4 is final printed, then reverted to pending for corrections, and finalized again, the draw number would prefil as draw 5 and the last auto number would show 5 in contracts. If the user changes it back to draw 4, the last auto number on the contract will remain at 5, as it has already incremented.

If your job and contract numbers do not align, we may need to refer you to your consultant or Sage Expert Services for help getting the information you need on your report.  

This worked perfectly, and I was also able to add the Retainage Rate to the report with this formula:

LOOKUP(Retainage Percent (CN Contract Item), Job (JC Job))

Thank you for your help!