Direct Database Lookup

randywb

Forums

I am looking for a way to convert a series of joins to a direct lookup. That will give me more freedom with the scripting and eliminate a lot of extra code. I attached an Image that explains what I am trying to accomplish.

Images

Brian Element Wed, 03/18/2026 - 11:53

Hi Randy,

Can you explain this a bit more.  I am not following how you get from the Keybank database to the TVMCSK database.  Where does the TVM amount come from, is that suppose to be from the Dollar_Amount field.  Maybe if you could explain the logic it would help out.

Thanks

randywb Wed, 03/18/2026 - 14:18

The data in the two databases will be loaded prior to this step

The way the process works is it does 4 joins with the date and amount from the Key database as the join fields.

Join 1 KeyBank Date amt ==>TVM Date1 and amount
Join 2 KeyBank Date Amt ==> tvm Date2 and Amount - Include all fields from above
Join 3 KeyBank Date Amt ==> tvm Date3 and Amount - Include all fields from above
Join 4 KeyBank Date Amt ==> tvm Date4 and Amount - Include all fields from above

At this point Idea duplicates the date and amount fields .
I interrogate the final db for valid values and rebuild the final bank database.
I put a date from TVM into a new field the Key Bank
I mark the TVM with an X to signify it was used
The process is changing and the window of looking forward is shifting. I thought it might be a good opportunity to switch to a more direct lookup and avoid the joins and simplify the code

I envision
Read through Key do a direct lookup in TVM update Key that it was found with a date. Update TVM it was found with an X. I did something similar before but it reads through the entire DB on each record. It works perfect, but not very efficient.

I hope this helps.