LOOKUP not on primary field help

Question

I have a report that is using the Job Transaction file as the sort order. I want to be able to add the units from the EQ - Rev Type Tran and the job from the EQ Trans. Since we have the interface active between EQ and JC but are NOT creating entries I can't pull direct from JC Trans. I've tried several LOOKUP's but I am not strong at them at all. Any help would be appreciated.

FLAkj92 · Accepted Answer

Yes, I'm using the JCT file as the primary because I'm pulling in Payroll and AP that have hit. Unfortunately, if I change the sort order to batch or anything else it eliminates my accumulating formulas for labor hours and such by job. Yes, a lookup works fine if I use say Serial # from EQM file and use Equipment as the key value in the JCT for a time period when Equipment entries were turned on but since they are turned off the problem is the JCT file has never "seen" the equipment. Currently, we use two separate reports. The equipment comes off EQ Rev Trans and EQ Trans while the other comes from JCT only. I would like to merge the two together.

Art Minds · Answer

A starting point to help you solve your problem is to understand the relationships between the various files that contain the data you want to appear on your report. Start by printing the Data Dictionaries for the various files involved in your design. Go to Report Designer > Tools > Available Fields and select the JC Transaction file, EQ Revenue Code Tran Record, and EQ Transacdtion Record. IF these are the not the files you described, choose the files that contain the data you require on your report. 
 
At the bottom of each report is a section titled "Standard Orders." These are the available standard sort orders. You mention in your post that you are using the Job Cost Transaction file (JCT file) as the "sort order". I believe you probably mean that you are using the JCT file as the primary driving record for the report, and perhaps are using the default sort order for that file, Transaction sort (shown as the first sort order, with the details of the sort listed). There is only one data field on the JC Transaction record that references Equipment, and that is "Equipment" identifying the piece of equipment that was related to the JC Transaction entry where it appears. For you to retrieve data from an Equipment record would require a many to one relationship between the JC Transaction file and the Equipment record. The only record that you could retrieve data from with your designated sort order would be the Equipment record, which would allow you to retrieve the Description, Serial Number, Make, Model, etc. from the actual Equipment setup record. 
 
There may be another sort order you could apply to the JC Transaction record (such as Batch Entry) that has a matching Sort Order in the other files to retrieve the EQ data that links to each specific JC Transaction (if there is a one-to-one relationship between the JC Transactions that originate from or contain EQ data, but the output would like be a long list of details. In that case, you would also need to apply a filter that only delivers the JC Transaction records where the "Equipment" field is not blank. 
 
Hope this helps you approach your report design problem with another perspective. 
 
Art Minds 
Pasadena, CA

Sage Construction & Real Estate

LOOKUP not on primary field help

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING