Change Record ID As Needed

[carguy86]

Get ready for a headache!

I have this wonderful Finance database that I have been working on (With your guys' help) for quite some time.

In this window (Table View) it shows all transactions for a given account.

The issue is that the program also manages "recurring transactions" which it automatically creates records for at the beginning of the month. Each record has an "ID" that is a sequential serial added at birth.

Problem is, (see picture), when the recurring transaction gets created, it's ID will be lower than another transaction that might occur on the same date, even though the recurring transaction cleared the account AFTER the newer transaction.

Now to complicate things, the ID is also tied to another table which houses categories, amounts, etc. So to change the ID in this table means also manually updating the record in the other table (Relationship is built on this ID field)

The table is sorted by Date first, than ID, in order for it to total correctly, the transactions must be in the correct order.

Basically, what would have to happen in this instance would be I would have to update both this table and the categories table to reflect Row 1 being ID 184, Row 2 - 104, Row 3 - 183 as the RED Pending transaction needs to be last on the list.

How can I make this work?

[comment]

the recurring transaction cleared the account AFTER the newer transaction.

How do you know this? Both transactions have the same date - and no time. They could have occurred in any order, and the order in which they are displayed is completely insignificant. Of course, if you have pending transactions, you can push them to the end of the day by including the status field in the sort order. BTW, sorting by ID does nothing since IDs are assigned in creation order anyway.

In any case, you should NEVER modify the auto-entered serial ID.

[carguy86]

I know this because of how they clear the account (in what order). I need them to be in the proper order to reconcile with statements.

Sure I can sort by status, but then what happens when all the statuses for a given date are "Cleared"? Then FM will revert to ID sort, which will be "wrong" due to the recurring transactions being created before daily transactions.

At the basic level, I need a way to rearrange these records within each day...I guess I could just create another field,"Order Number" and add that to the Sort instead of the ID...and manually enter the value?

The order is significant because of the running totals in Total2 and 3 (Last two columns)

[comment]

I guess I could just create another field,"Order Number" and add that to the Sort instead of the ID...and manually enter the value?

Yes, that's what you should do. If you like, you can have buttons to shift a row up or down. In any case, the re-ordering must be done manually, unless you know in advance how the statement will be ordered (for example, you could mark recurring transaction when you create them, so they'd always be the last ones on that day).

AFAICT, my bank has no internal order of daily transactions; if I do a deposit and a withdrawal on the same day, the interim balance can be positive or negative as the chips fall. So if I wanted to re-order my records to match their statement, I'd have to do it manually while looking at the statement - or import their transactions to replace mine.

[carguy86]

Is there a way to add buttons to a table row? (I know this can be done in Portals) Or would I have to change it to list view an mimic the table view?

[comment]

Technically, you could use a dummy field with a script trigger attached to it. But a list view would make more sense, IMHO.