Hello! I have a report design that utilizes the following recordsets:
- CN Contract
- CN Contract Item
- JC Job
- PJ Job
- PJ Job Custom Fields
I have a custom field in PJ Job Custom Fields that specifies the price structure (stipulated sum, GMP, etc.)
My report design allows me to insert fields from PJ Job field list via JC Job. However, it appears that there is no similar correlation to PJ Job Custom Fields.
PJ Job Custom Fields requires the Job Index as a key. I am able to place the Job Index on the report as a field by inserting it and selecting the appropriate key record source. However, when I try to use it in a formula, it tells me there are multiple methods to retrieve it and that a lookup formula is necessary. I've tried using the Job (Job Number) field from CN Contract Item and JC Job, but it tells me that the format of the key is incorrect (even though they're all alphanumeric and the same length!). If I use SWITCH on the Job field, it allows the formula to be saved but returns a null result.
Am I misunderstanding how the LOOKUP function works or is this a software error? If the Job field is a primary key in PJ Job, shouldn't I be able to use the equivalent field from either CN Contract Item or JC Job to query the Job Index? And once I've queried the Job Index, shouldn't I be able to use it to query additional data from PJ Job Custom Fields?
Thanks in advance for any assistance!!
