Posting to another table

[Judith]

Hi,

I want a table (A) to post to table (B) by entering .T. into a logical field if an invoice number in table (A) matches the invoice number in table (B). I have tried to use the field rules but it says that the rule is invalid. Is there a way I can get this to work please.
Judith

[Ted Giles]

Look at the Posting Rules, Judith.
You can set the Conditions there. alpha1.png
Test for the logical relationship and possibly set a variable.

The way this app works is that a lot of expenses are entered and when approved, posted to the rolling budget.
You could automate it, but I do it manually and show a TICK beside the poisted items.

[Judith]

Hi Ted,

Thank you for getting back to me. I used the posting tab in the field rules, I selected the table to be posted to - invoices - and the field 'Clear' to be posted to. I then selected the target linking field as the invoice number and the source linking key as the invoice number and the operation to Replace with the condition .T. (True).
When I try to enter a new record I get an error message, Cannot post to table INVOICES. Post rule is not valid. I can't figure out why it wouldn't work?

[Ted Giles]

I will try and replicate the issue, Judith.

[Judith]

Thank you Ted

[Judith]

When I create the rule it says 'The target field must have the same type as the source field'

[Ted Giles]

I cannot get around that restriction either. Tried many different ways, so the only suggestion I can make is to create a Post Operation with a condition attached.
That's how I got around it.
So in my experiments, I have 2 tables - Source and Target.
If Source Inv no and Target Inv no exist and Cleared = "y" in Source, then the post works.

You may have to put the operation into the OnSave Event in a Form

If you want the MMouse example, just shout, but I expect you know what I'm getrting at.

[Judith]

Thanks Ted, it's a relief to find I wasn't the only one finding that restriction. I have gone on to create a script which appears to be working (I'm still working on it).

[Ted Giles]

I find that startingwith the Operations/Posting is best as it gets the structure correct.
View and copy the Xbasic into a Script if you need to.
I willtry with 10.5 as I remember having this working in the past.

[Judith]

Thanks for the advice Ted, I have used the Operations/Posting route to get to the script

[Ted Giles]

Whoopie!
It does work after all. I knew I had done it in the past.

See attached Judith.
Notice the field selected in the Posting Rules - not what you would expect.
Also, created a Lookup so that only records which have a matching relationship can be updated - unless and until, the target table is updated with a reciprocal field.
Posting Test.zip

Off to celebrate with home brew!

[Judith]

Hi Ted,

I have looked at the Posting Test files that you sent me, and I agree that it works fine - we do use such examples in our database. The problem I'm having appears to be because in the Field Rules I am trying to post to a logical field. I want to post .T. to a field called Cleared if the invoice number in the master table matches the invoice number field in the transaction table. When I go on to enter a record in the table I get an error message to say that the rule is invalid.

[Ted Giles]

There is an easier way, Judith.
I will experiment further in Field Rules but could you live with a Set consisting of the Invoice table linked 1:1 with the transaction table and have a tick or whatever to indicate Paid?

[Judith]

Hi Ted,
We do have a set that you describe but my MD is very keen that the action of entering a payment on a table marks the invoice as paid in another table. This is to avoid any human error (forgetting to tick the field in the invoice table).

[Ted Giles]

Got it.
Let me see how efficient a process I can suggest.