Solved: Problem with redundant, erroneous records

Joseph_Seddik · 12-31-2021 11:08 AM

Estimados

I’m facing this problem that I’m unable to solve and would really appreciate your help.

I have a Transactions table that is referenced by many others: Payments, Withdrawals, Pays, Deposits, etc. The user will use a Form to fill in the details of each of these tables, and in the process he will also fill in a reference field to the Transactions table to create a new corresponding transaction.

The suggested values of the referenced Transactions field in all other tables is set to LIST(), so that the user is forced to create a new transaction not use an old one. The user creates a new Transaction and upon saving this subform he goes back to fill in the remaining details in the parent form.

The problem is sometimes the user fills in say a wrong amount, so when he is back in parent form, he enters again and create another Transaction record. The result upon saving the parent Form, is that we end with two (or more) Transaction records in the database, only one of them is referenced correctly by the parent table.

What I’ve tried so far:

Attempted what I can think of controls, Show_if, Valid_if, Zero default value, etc. to no avail.
Tried to use Bots to search for unreferenced Transactions recrds and delete them. Possible I’m doing something worng, but the result has been completely unpredictable.
Tried to make the child form itself delete the unreferenced record but the result was that all records are deleted, probably because it seems while you have the reference set in the memory and you can use it correctly, at the time you save the form it looks for it in the table, however, I observed that most of the time the child records (all of them) are saved to the source before the parent table, so looking for unreferenced records results in all of them.
Made several attempts to pass the value of key column by the parent table to the child but couldn’t figure out a way to do it.
Activating the option: reset to default value upon edit. No change.
Filter out existing rows. Didn’t help.

What do you suggest please? Thanks much!
KJ

Joseph_Seddik

Hello @Suvrutt_Gurjar

I’m glad to tell you the problem is solved, inspired by on your insight

In each of the referencing tables: Payments, Deposits, etc. I added the exact statement below as the Editable_If formula for the column: transactionID, that is referencing the Transactions table:

NOT( IN( [transactionID], Transactions[transactionID] ) )

Now the user has only one shot to edit the transactionID field in the Payments form to create a new Transaction. Once it is created and the user is transported back from the Transaction form to the referencing Payments form, the user can no longer go back and edit the transactionID field again. In that case he will have to abandon the original form completely and start over, thus avoiding the creation of redundant Transactions records.

Thanks my friend!

View solution in original post

Joseph_Seddik

Hello,

I managed to find a solution through automated post-saving deletions, for the records created within 1 minute from the last saving.
I wonder though if there is a preventive way before the creation of such records. Thank you.

KJ

Suvrutt_Gurjar

Are you saving any references ( meaning record values ID or transaction type -payments, withdrawal etc.) of the parent record in the transactions record, when the user creates the sub-form?

Joseph_Seddik

That is point n. 4 and that would be useful, but I can’t figure out how to do it.

It is actually the other way round, the Transaction sub-form puts the calculated ID value as a reference in the parent Payment table. If I can calculate the ID in the parent table and push it to the child table, keeping the reference from Payments to Transactions, that would be great.

Other than the ID field, the two tables share no other information.

Suvrutt_Gurjar

Okay thank you. If understanding of your requirement is correct, can you please try the below expression in the transaction reference column’s Editable_if constraint, the below expression?

NOT(IN([Payments Table ID], Order Details[Payment Table ID])) for Payments table

NOT(IN([Deposits Table ID], Order Details[Deposits Table ID])) for Deposits table and so on? ’

[… Table ID] is the key of respective tables. Please test well first for one table for all possible operations.

Joseph_Seddik

Thanks to you actually!

OK, before anything, I will test carefully and come back with the outcome. Thanks again.

Suvrutt_Gurjar

You are welcome. Please do post back your results/observations.

Joseph_Seddik

Hello Suvrutt,

Apologies for late reply. Haven’t had the time to test it until now. I’ve tried different combinations based on your kind suggestion. It would help me if you could clarify please what did you mean by “Order Details”. I don’t have such table. Thank you!

Joseph_Seddik