Compare & Link using date range as a criteria

User Talk Formulas
1

Hi, I am not sure if there is a solution for this. I am trying to do a compare and link between two tables. I however want to use a date range as one of my criteria rather than a set date. My other criteria is that the Amount must be the same this one is fine.

The reason why I want a date range by a couple of days is that the date on my receipt might be a day or two before the date on my bank transaction list. By combining a date range and the exact amount the links should be fairly accurate.

I know compare and link does not have that option. Could this be a consideration. Otherwise is there another way I could possibly get this right?

Thank you :slight_smile:

2

Hi Helene,
I think a python script could be used to achieve your goal of adding links based on equal amounts and date within a date range.
If you want help to write it, we’ll need some more information:

  • both tables’ names
  • the amount columns’ names for both tables
  • the date columns’ names for both tables, and the “way” you want to compare: should the date from table1 be within a date range based on the date from table2 (+/- N days from the date) or the data from table2 be within a date range based on the date from table1 ?

Bests,
Benjamin

1 Like
3

Thanks so much Benjamin, that will be really appreciated.

Table 1 Name: Requisition
T1 Date Column Name: Date on Receipt
Table 2 name: Bank Transactions
Table 2 Date Column Name: Transaction Date
Both tables Amount column is called Amount
I would like the range to be less than 5 days (up or down) from table1 Date on Receipt if possible.

4

Hi again Helene,
Here is a first try I forgot to ask you the name of the link column in both tables, so I created a variable LINK_COLUMN_NAME_IN_TRANSACTIONS_TABLE in my code (you’ll have to replace 'Requisition' by the appropriate column name, always surrounded by simple quotes.
Please note that in order to run properly, both date columns should use ISO date format.

from seatable_api import Base, context

server_url = context.server_url
api_token = context.api_token

LINK_COLUMN_NAME_IN_TRANSACTIONS_TABLE = 'Requisition'

base = Base(api_token, server_url)
base.auth()

link_id = base.get_column_link_id('Bank Transactions',LINK_COLUMN_NAME_IN_TRANSACTIONS_TABLE)
transactions_table_name = 'Bank Transactions'
requisition_table_name = 'Requisition'
row_id_list=[]
other_rows_ids_map = {}
transactions = base.query('select * from `Bank Transactions`')
for transaction in transactions :
  requisitions = base.query("Select * from Requisition where Amount=" + str(transaction['Amount']) + " and abs(dateDif(`Date on Receipt`,'" + transaction['Transaction Date'] + "','D'))<5")
  if len(requisitions)==1 :
    row_id_list.append(transaction['_id'])
    other_rows_ids_map[transaction['_id']] = [requisitions[0]['_id']]
try :
    update = base.batch_update_links(link_id, transactions_table_name, requisition_table_name, row_id_list, other_rows_ids_map)
except :
    print("Error while updating links")

if update['success']:
    print('Script successfully terminated')
else:
    print('Script encountered an error')

Tell me if you encounter any problem (you probably will! :sweat_smile:).

Bests,
Benjamin

5

Thanks so much Ben, the script runs without a hiccup, but for some reason it is only matching about 20 out of 500 transactions. Here is an example of one that I think should pull through but doesn’t. Can you see from this and the script what might be wrong?

Screenshot 2024-11-21 at 12.52.36
Screenshot 2024-11-21 at 12.52.361198Ă—424 36.1 KB

6

Hi Helene, glad to here that the scripts runs at first attempt, I’m getting better :sweat_smile:
Unfortunately, I can’t figure out why your example is not matching: when I put these data in my test base, it matches…

In this script, I link transaction and requisitions when i find one single match (if more than one match is found, nothing will happen), and I think this might eventually be the problem. Can you look in your database to see if you can find more than one requisition corresponding to this transaction, or none, and tell me? To do so pretty easily, you can activate the SQL query plugin and launch this request :

Select * from Requisition where Amount=778.37 and abs(dateDif(`Date on Receipt`,'2024-11-06','D'))<5

Bests,
Benjamin

7

Hi Benjamin,

Thank you for checking it for me. I did the run and it only found one match, so it is very interesting I don’t know why it isn’t matching :thinking: