Hello!

I'm trying to compare 2 tables, for missing records, and then filter the results to show on a custom report only the records that match, excluding the missing / deleted records. The problem is that the report has already a structured logic (it's the report that shows the Hours for the General Tasks and the Production Hours).

Now, the standard report takes the information from the bptmm100 table, but when some records are deleted from the General Tasks session, through the session, for some reason they don't get deleted from the bptmm100, but only from the bptmm111, resulting in showing on the report bad information for the users (hours that were already deleted). I want to filter that information.

On the details of the script I was thinking in something like this:


detail.9:
before.layout:
select bptmm111.*
from bptmm111
where bptmm111.emno = :bptmm100.emno |Employee Code
and bptmm111.year = :bptmm100.year |Year
and bptmm111.peri = :bptmm100.peri | Period
and bptmm111.type = bptmm.type.general |General Tasks
and bptmm111.hrea(8) = :bptmm100.hrea |Hours, Comparing the 8th number on the array
selectdo
if isspace(bptmm111.emno) then |when the hours are not found in the bptmm111, the query
|returns an empty string in the employee code, so this is how I
|filter the hours that I don't want to show on the report
lattr.print = false
endif
endselect


The problem with this code, is that for some records it works, and I'm able to NOT show the REAL missing hours, but for some reason, I don't know if because a recursive search is needed, considering also how the data it's sorted differently in both tables (bptmm111 & bptmm100) and the sequence numbers are different (bptmm100.seqn & bptmm111.seqn)...so for some reason in some cases it also filter (doesn't show) the hours that are in both tables, so it also filter the REAL hours that I need to show on the report.

Any advices???

Thank you very much,

Daniel Romeo

You say the records are only deleted successfully from the bptmm111 table - so that means there is still something in the 100 table. Yet the query shows an empty employee number. So are the records really deleted or is it just clearing the employee number? I am not familiar with these tables and my first thought is why couldn't you just query 111 for an empty employee number? Yeah - it might mean full table scan and be slow, but if it got you data to fix it might be worth it.

You say the records are only deleted successfully from the bptmm111 table - so that means there is still something in the 100 table. Yet the query shows an empty employee number. So are the records really deleted or is it just clearing the employee number? I am not familiar with these tables and my first thought is why couldn't you just query 111 for an empty employee number? Yeah - it might mean full table scan and be slow, but if it got you data to fix it might be worth it.

Hello Mark,

Thank you very much for your reply :)

So, the whole records are really being deleted from the table bptmm111 while they are not for the table bptmm100 --> regarding the empty employee, I just understood, while doing some other tests, that it's because of the logic of the report, that carries also the logic from the session --> so if you print from the session of the Hours Accounting, that doesn't have any specified employee, with the logic of the query that I added, it throws an empty string for the employee when it doesn't find the match of the hours on both tables, that is also part of my problem: that the query I added is not matching hours (so records) that really exists on both tables, for the same employee, year and period... that maybe are sorted differently on the tables???? I don't know :(

Example 1. --> Printing from the session with a specific user on the session. Notice how I want the report printed for the employee code 1026, but when the query doesn't find the match returns on the report the employee code from the session, that is bsp in this case.

https://ibb.co/eVhQfy

https://ibb.co/errzqy

Example 2. --> Printing from the session with a blank user on the session. Notice now, how I always specified for the report the employee code 1026, but this time instead, when the query doesn't find the match for the hours, returns an empty string.

https://ibb.co/m3UNcd

https://ibb.co/mPPzOJ

Now, on the table bptmm111 for the same employee, same year and period, there are 5 records...for the table bptmm100 for the same employee, same year and period, there are 8 records...so 3 more from the table bptmm111. So the whole point is that I don't want the report to show these 3 extra records, that have already been deleted.

Thank you very much!!!

Daniel Romeo

Can you attach the JPG's straight to the post. I can't see them using the link. You can do this clicking on go advanced, manage attachments. I do not have these tables to look at. Without being able to see the table structures it makes it tough to figure out how to go from one to the other to see what records it has and how they match up.