0

A row of information is missing if the JE no is the same

Jet Reports Historic Posts

October 07, 2013 23:45

None

Follow

If under a Journal Entry, we have posted the same amount twice with same account numbers, one of the line is missing when running the Jet.
For example, JE123456 has Dr 1200 1000
Dr 1200 1000
Cr 4000
After running the Jet to provide GL detail, under account 1200, $1000 will only appear once but not twice. How should I fix this error?

Thanks!

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Jet Reports Historic Posts October 08, 2013 14:39

  I'm not sure exactly what you mean. It sounds like you just need to include the primary key of the table in your formula so that Jet doesn't eliminate the duplicate results. What does your formula to replicate the values look like?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts October 08, 2013 16:22

  Hi there,
  
  Here are my formula:
  Under Account Number: =NL("Rows=4","AccountTransactions","Account Number","Account Number",Options!$D$12,"TRX Date",Options!$D$10)
  This is the formula to pick up details transaction: =NL("Rows","AccountTransactions",$F$2:$O$2,"TRX Date",Options!$D$10,"Account Number","@@"&$B6)
  
  Looking forward to hear back from you.
  
  Thanks,
  Louise

• 0
  
  Jet Reports Historic Posts October 08, 2013 17:18

  Hi Louise,
  
  I don't know the structure of your database, so I don't know what the primary key of the table AccountTransactions is. Basically the solution you need is to include this field in your second formula. Right now you are using the range F2:O2 in the Field parameter and all the fields being returned are in those cells. You should just expand this range and add the primary key field from the AccountTransactions table. It might be named something like "Transaction Number" or something similar (this is just a guess). You can hide whatever column you put it in if you don't actually want to see that information on the report, but you need the primary key in order to retrieve all rows from the table. Does this make sense?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts October 08, 2013 18:15

  Hi Hughes,
  
  It makes sense and I will have a try.
  
  Thanks very much.
  
  Regards,
  Louise

• 0
  
  Jet Reports Historic Posts October 08, 2013 22:35

  Hi Hughes,
  
  Just want to confirm by adding the primary key, will this able to fix if the duplicated amount are under the same Journal entry no. with same audit trail code. For instance, my journal entry is:
  Dr 2000 $500
  Dr 2000 $500
  Cr 4010 $500
  Cr 4010 $500
  
  I have added a primary key but since the $500 under GL2000 is under the same Journal entry no. and audit trail code with exact same description. Therefore, there is no unique code to retrieve both $500.
  
  Can this be fixed?
  
  Thanks

• 0
  
  Jet Reports Historic Posts October 08, 2013 23:05

  Hi,
  
  The primary key of a table is always guaranteed to be unique. Therefore if you are really retrieving the primary key, then you will always get all the records that match your filters and nothing will be removed. It is a rule in databases that the value or combination of values of the primary key fields must be unique. If you are still having some duplicate records eliminated then you have not truly added all the fields of the primary key to the Jet query.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts October 09, 2013 23:16

  I got it now. Thank you so much for your great help!