Mark Exist field help please.

[Keith Hubert]

Hi Guys,

I've never needed to do this before so not sure if there is an easy way to do it.

I have 230 records in one table and need to update a couple of fields in the Master table, using Post operation linking on Product_Id.

I'm getting 228 records done, which means there are two records missing from Master table which are not being found to update.

Thinking of using Exist and Mark to find and mark unmatched records.

All suggestions welcome.

TIA

[Stan Mathews]

Re: Mark Exist field help please.

Quick and dirty? Add a field to the transaction table, at least temporarily. Run post operation reverse of what was designed before. Post value to the new field. (If the added field is character, post "X", etc). Records with no match won't be posted to that field.

The post operation won't care if you don't actually use a value from the transaction table. If you wanted you could post left(some_character_field,1) where some_character_field is a field in the transaction table, but it isn't necessary.

Actually your outcome is the design feature of a subtract operation.

Overview of Subtract Operations

A Subtract operation takes records from the master table which do not have matching records in the transaction table and copies them to the result table. The result table can contain fields from either the master table or the transaction table, but in all cases the transaction table fields are blank.

There also exists the possibility you have two records duplicated (in what you are using as the transaction table) and there should only be 228 matches.

[Keith Hubert]

Re: Mark Exist field help please.

Hi Stan,

Thanks for your very quick reply.

I sort of did what you suggested by setting a field in the small table to Logical and did a post operation from the Master, if there was a match then it was .T.. This found the two records and I was able to do the updates as required.

I should have given more thought before posting. Maybe this will help someone else in the future.

[Stan Mathews]

Re: Mark Exist field help please.

There are so many options available that sometimes it can seem overwhelming. I try to start with the simplest avenues first.

What was not intuitive, to me when I learned it, is the use of "constants" in the posting operation. The same can be done with appends. A value appended to a table need not be a value from the transaction table. You can "append" to a date field in the master table the current date, creating a record of when the record was appended. Something similar can be done with update operations. Suppose you crosstab a table (say salespersons by month) yielding a numeric value and all you really want to see is which records have a value = 0 in one or more months. For the purposes of this process you don't care about the actual sales number). Run an update on the crosstab result and for each month field replace the current value with 999 if it is > 0.

The result makes it much easier to spot the problem months.

[G Gabriel]

Re: Mark Exist field help please.

You could have just queried the table for the fields that were not updated and those two records would have shown up right away.
Curious as to why they were not update?
Did you use a filter?

[Keith Hubert]

Re: Mark Exist field help please.

Gabe,

I did not use a filter, the issue turned out to be blank characters. Even though I used Alltrim() it made no difference, that is why I had to fine the actual records.

Stan, I do use "constants" quite a lot in posting and updates. One thing I do is add in a Tax rate which is picked up from a table in a global variable from startup Autoexec. This is then used as a default for a new product. This value is entered automatically without the user having to enter this when creating the Invoice.