Posting to another table

[Tom Cone Jr]

Judith, if you're looking for a batch operation Ted is on the right track. If you're looking for something that happens when the user marks an invoice paid in one table, but wants that stored in a second table, too, here's an easy example. Hope this helps. Start by opening Form A


For_Judith.zip

[Ted Giles]

Sorry, Tom, I tied that method originally and it only works if the whole record is entered - including the .t. in the true/false box. Ticking Paid later doesn't trigger the post.

I do not understand the logic of flagging an invoice as Paid in a separate table as somewhere and sometime ONE of them has to be tagged as Paid.

What I believe Judith is asking for is a list of invoices which can be flagged as Paid and when that happens, write the fact to a separate table which also holds the invoice numbers.
That's what her MD has asked for.

Judith, could you elaborate please as maybe Append might work better in this instance?

[Tom Cone Jr]

Ted, permit me to quibble. The post field rule fires only when the record is saved. It is not triggered when the onchange event of a field object fires. i.e. by ticking the field object

In my example if you create a new record in FormA but leave inv_pd field empty the post fires when the record is saved... but does nothing because (a) there's no corresponding record in tableB; and (b) there's no value in in the inv_pd field to be posted. If you then create a corresponding record in TableB, but leave inv_pd blank, the post will occur if you go back to FormA and change the record by supplying a value to inv_pd.

This is a limitation of the post field rule. It fires only when user is entering data from the keyboard and a new or changed record is saved.

From the help: When you use a posting Field Rule, the posting occurs during data entry each time you enter a new record or change a record. Target field values are always up-to-date. Contrast this with using the Post Operation, in which a batch processing operation is performed. All matching values for multiple records are posted when the operation is run.

Post operations performed with field rules only occur when data is input through an Alpha Anywhere form or browse, not through an Xbasic operation or Web Application.

[Ted Giles]

Yes you are correct Tom.
The implication is though, that invoices are entered as unpaid, and when paid as by .t. entry, table 2 is updated.
That's the problem as I see it.

[Tom Cone Jr]

Ted, we may (or may not) be saying the same thing. I share your concern with the app design. However, the post field rule is working just fine, within its limitations.

[Ted Giles]

Yes indeed. Ther Post rule is doing what it should. I tried adding a conditional comment in an Action Script to fire the post but it ignored it and fired anyway.

I showed the option of putting a Set together with the data from the input and result where the .t. could be entered, but that isn't quite what Judith seems to require.

In some Local Authority apps, I have a billing and payments reconciliation method which compares the income and outgoings with the bank statements.
That way we can see the actual bank balance, the amount available (on hand less cheques issued) and the amount available in the future (on hand less cheques issued plus invoices raised).

The finances have to be accounted for publicly and are scruitinised monthly, so need to be adequately policed.

I think we both know how to do this, but everyone has a boss (as my wife says).

[Ted Giles]

Seems to me an Update operation is needed, not a Post operation.

[Al Buchholz]

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?

Judith

Here is an image of the field rule that Tom Cone provided in his example.

I adjusted the width of the columns to show the entire field rule.

Can you do the same for the field rule that gives the error message that the rule is not valid?

Or upload an example that shows it, but leave out sensitive information.

We can work out the issues if more information is provided.

These threads go on forever without examples. TonConePostingFR.jpg

I'm thinking the issue is that you are trying to post to a logical field using the value in the condition instead of having a logical field in both tables.

The condition column is not the value that is posted into the target table.

[Judith]

Sorry everyone, I hadn't received any notifications to say there were further threads on the topic, I will read through them all and get back to you.

[Ted Giles]

I posted this about 6 hours ago and it didn't stick.

Posting Test01.zip

Try this as an Update.
It takes variables from the primary form and updates the target form when "PAID" is changed.
You can add invoice numbers to the target data set, so you can trace changes.

[Judith]

Hi Everyone,

I have read through all the posts and opened up the tables people have sent me. The penny has dropped, for posting to work I would need a logical field on the payment table so that it would post to a logical field on the invoice table. Is that right? I was trying to post asking Alpha to use a numeric field as the point of entry and compare the invoice number in each table and then post true in a logical field in the invoice table.

I have solved the problem by creating a script that runs at the end of each day which posts paid on the invoice table if the invoice number on the payments table matches the invoice number on the invoice table. It appears to be working fine.

Thank you everyone for your help.
Jude

[Ted Giles]

Glad you have a solution and it will work as it runs against the whole data set.
Doing it record by record is possible using Update but I found changing one logical field problematic on its own.
Anyway, as long as it works, happy days.

[Al Buchholz]

Judth

You can build a cansave event for the form ( or in a field rule cansave for the transaction table) that uses action scripting that calls the update operation and filters for the invoice number.

[Ted Giles]

Yep. My basic demo didn't link it to the OnSave.
I think thats what I'd do.
The end of day process would sweep up any issues.

In my Accounts apps, I have a complete rebuild operation which sorts stuff out when a user gets trigger ( keyboard) happy.
Sometimes rapid data entry can cause issues at the PICNIC end, so this approach can help.