Append operation using Unique - how to get it to work

[Ray in Capetown]

Scenario:
Multiple bank accounts are monitored throughout the day for deposits and withdrawals to post into various ledgers and customer accounts.
Clerks log into various banking sites continuously downloading CSVs.
Administrator using Alpha, periodically selects csvs to update into the system. They may select the same csv as earlier (to ensure picking up added transactions) as well as most recent csvs
Requirement:
When bank transactions are collected it must not allow duplicate that transactions, only new entries appearing since last update. The bank systems dont always append to the bank statement, earlier entries picked up later can be inserted before a previous one.

None of the above is strange or unusual, but I cannot get the append operation to append only unique;
Determined by fields iDate (c)+ iAmount (n) + idesc (c)
it either appends nothing or all again
Simple DB attached. bankcsv.dbf must update to banks_imported,dbf.
Then Add a or alter a transaction with an amount in bankcsv.dbf. Update again and only the new transaction/s should be appended.

I included only a couple of my attempts.
All the other ops and code work flawlessly from origin to account journals - except for this little bit in the middle.
My only alternative otherwise is to code a laborious procedure.

[Tom Cone Jr]

Re: Append operation using Unique - how to get it to work

Your field iAmount is 14 wide in one table but 15 wide in another. Your uniqueness expression assumes they're both 15 wide. At present the unique op is trying to compare 15 wides against a field that is only 14 wide.

[Ted Giles]

Re: Append operation using Unique - how to get it to work

Date and Description are also different Ray.
You could build a Index by trimming the data and then use the EXIST feature to exclude data that is already present if you cannot control the CSV input field size.
Or, move from CSV table to a table in the format and size you want, then Append.

[Ray in Capetown]

Re: Append operation using Unique - how to get it to work

Thanks so much Tom. i had recreated these tables so many times I overlooked checking.
Now I see too that the idate char field is different in length.
I will follow up on that lead.

[Tom Cone Jr]

Re: Append operation using Unique - how to get it to work

Ray, let us know how things turn out. Ted's got a good point. Check all fields involved in the append beforehand. If the "link" expression fails a record will be omitted. If the "link" expression is ok, the append will occur but some data may be truncated. So, compare structures of master and transaction tables beforehand.
l

[Ray in Capetown]

Re: Append operation using Unique - how to get it to work

Yes I see Ted. Embarrassing.
I can control the csv table from the import. i have to pay more attention, sloppy.
I will revert after I have "unpickled" myself

Thanks

[Ray in Capetown]

Re: Append operation using Unique - how to get it to work

Will do

[Ray in Capetown]

Re: Append operation using Unique - how to get it to work

After correcting the field lengths, the APPEND still duplicated all records including the new ones. Maybe a mixed field match is too complex, and I didnt want to change a bunch of imports and tables to make all amounts Char..
Experimented a bit and eventually wrote a function that does exactly what is required to replace the APPEND operation, with added advantage of not inverting records as APPEND seems to do. It's quick because it can implement LQO with an index on Idate
You can try it with the sample DB in #1 - save the function below and call it from Interactve Editor

FUNCTION bank.csvview_add AS L ( )
dim r2v as p
tbadd=table.open("banks_imported")
tbcsv=table.open("bankcsv")
tbcsv.query_create("","iamount<>0")
tbcsv.fetch_first()
while .not. tbcsv.fetch_eof()
	vfnd=tbadd.query_create("","idate+IAMOUNT+idesc="+quote(tbcsv.idate+tbcsv.IAMOUNT+tbcsv.idesc))
	if vfnd.records_get()= 0
		tbcsv.record_to_vars(r2v)
		iserr=tbadd.enter_record(r2v)
		if iserr.has_errors .or. iserr.has_warnings
			ui_dlg_box("POSSIBLE ERROR","Please check entry "+r2v.idesc)
		end if
	end if
	tbcsv.fetch_next()
end while
'parentform:BROWSE1.resynch()
END FUNCTION

[Ted Giles]

Re: Append operation using Unique - how to get it to work

This works Ray.
I created a new Table from the CSV table, changed the order of query for the Unique test.
Looks like the order of the Unique query needs to be specific.
Now I'm off to clean my gun. Been shooting this morning!

[Ray in Capetown]

Re: Append operation using Unique - how to get it to work

Excellent.
I will study what you did.. thanks Ted.
What you shooting at?

[Ted Giles]

Re: Append operation using Unique - how to get it to work

Clays today. Take a long time to cook, longer to eat.

[Vince McGinnis]

Re: Append operation using Unique - how to get it to work

Ray,

Yes, the field lengths was probably the issue because when I changed both linking Key expressions by changing concat symbols from pluses to minus's, it started working.

ie: idate-str(iamount,15,2)-Idesc

[Vince McGinnis]

Re: Append operation using Unique - how to get it to work

Ray, one issue on appending unique records that is worth noting: You have to account for the possibility of two or more legitimate identical transactions.

I also use 'append unique' in my finance app when downloading and appending bank transactions. An issue came up a couple of days ago where I had to change my linking key/uniqueness expression. I take the train to and from work daily and I pay the same amount daily. However two of the transactions posted on the same day, instead of on separate days like they should of. Therefore one of the charges would not append as my linking key/uniqueness expression uses the same basic fields as yours: posting_date. Description, and Amount. To test for truly a unique record, since there are no transaction IDs associated with each bank record, the only thing I could do was add "balance" to that linking key. That way if I have two legitimate identical transactions on the same day, one will not be excluded because the remaining account balance will be different.

[Ted Giles]

Re: Append operation using Unique - how to get it to work

Suggestion.

Capture the transaction time in a TIME field.
Create calculated fields in the Banking and the CSV tables.
The calc field = a trim of all you need to make the record unique.
Use the calculated field as the Unique identifier as it should be the record DNA.

Rerun the Rules in the CSV table, and also the Banking table before Appending.

Just amount/date/reference (unless unique at the source end) just isn't sufficient.

[Vince McGinnis]

Re: Append operation using Unique - how to get it to work

Ted, I am not sure how to accomplish that. I don't know how the banking system works, but it appears the ACH transactions are accumulated for some number of hours and then "batched" into, or over to the bank site. So, two transaction that may happen 24 hours apart appear to be batched at the same time. The data that the banks provides is limited to only the 4 fields mentioned.