I have a table file with two records, put side-by-side. The data are addresses from two different systems, we're trying to find differences between those systems.

Typical data would look like:

        | Source
        +-----------------------------
        | Foo2000     | BarSYS
--------+-------------+---------------
Name    | Foo         | Foo
Address | Barstr. 1   | New Barstr. 1
ZIP     | 1234 QED    | 1234 QED
City    | Barshawa    | Barshawa
Country | Westland    | 
Phone   | 12345467890 | +98 34567890
Fax     | 1234567891  | 

What I need is to define styles for when the left side (Foo2000) is different from the right side (BarSYS). Either record can be missing as well.

In the above example, I'd like to colour both 'Address' values red, as well as the left 'Country' value, the 'Phone' numbers and the left 'Fax' number, because they are different between the columns.

How do I identify those fields - and whether they differ - in my style definitions?

P.S. I got this output by DEFINE-ing a source-name for each table and using MATCH FILE to combine them. The columns are the result of an ACROSS on source-name, while the rows are the fields from the MATCH, using OVER to turn them into rows.

This message has been edited. Last edited by: Wep5622,

Perhaps a car, employee or videotrk example to produce a similar data set if one could be easily created?

- ABT

Instead of using OVER with a series of distinct fieldnames, transform the data so that it can be reported as

...
WRITE 
 OLDVALUE 
 NEWVALUE
BY FIELDSEQ NOPRINT
BY FIELDNAME

That will allow you to style the 3 columns (all rows) with a single style spec conditioned on

WHEN=OLDVALUE NE NEWVALUE

The transformation generates multiple data rows from a single input row; use McGyver (which will also supply the field-sequence field)

MacGuyver... Oh dear!

I've been trying to wrap my head around The MacGuyver Technique, without much success so far.

I'm pretty sure there are some errors in the article, which just makes it more confusing. It's hard enough to understand without those errors, I'm certain. It would help if someone could fix those at least. I'll try to explain where I'm having difficulty with the article in question, any criticism is supposed to be constructive; I'd like to understand this technique, not to upset anyone.


For example, the article says "Figure 3 illustrates the code that will be used to", but when you look at figure 3, it says "FIGURE 3 - DIAGRAM OF NEW CAR REPORTING STRUCTURE". So apparently figure 3 isn't what I should be looking at, but what is? I can't find anything resembling the code I expect.
There is code in Figure 5, but that seems unrelated. Is it a reference to Figure 2? I have no idea what that's about (Are CMS and MVS operating systems, just like UNIX,VAX,etc are?).


Then there is an example query below Figure 3 (or is that part of Figure 3? I can't tell) that contains:

PRINT  COUNTRY CAR MODEL BODYTYPE COUNTER 
WHERE COUNTRY LE 79

Shouldn't that read: "WHERE COUNTER LE 79"? I'm pretty sure it should.


It certainly could do with a few pictures too.
I didn't understand the paragraph after Figure 2 (an explanation of what's considered a record) until I opened the CAR master-file and went looking for the mentioned fields. A picture of the exploded segment/field tree of that master-file (with the relevant fields highlighted) would have helped.

A notion that records in FOCUS apparently have a dynamic length (based on what segments are required to fulfill a request) would be useful too. I drew this conclusion from pondering that paragraph for quite a while, but I'm not even certain I drew the right conclusion.


Highlighting the important parts of the commands in the examples would be helpful. It took me a while to find where that BLANK column in the CAR file (below Figure 3 again) was supposed to come from, until I finally thought "whatever" and read past that JOIN and found the DEFINE.

I also wonder what is wrong with a blank line here and there, or some indentation? Such things are unfashionable in the FOCUS community it seems; For example, I just recently got an astonished reply from tech support why I was using tabs in my -DEFAULT statements between my variable names and the assignments (which triggers a bug, it turns out). Is that really such an unusual thing to do?


Next, it took me a while to figure out that FSEQ is just the name the author chose to use for that file, and not some FOCUS command I wasn't familiar with. That's an unusual name for an example-file, and a little hard to identify as a name - Examples I'm familiar with often use "Foo" and "Bar", to name a few.
I guess part of the problem is that if I see code in all upper-case (or all lower-case, for that matter), I find it a little difficult to identify where the different parts start and end.


Then, just before Figure 3, there's some talk about (respectively) the first column and/or the first byte of some file being blank. I have no idea what that's all about. What does that even mean, a blank byte? Is that 0x20(32) or 0x00(0)? If it is a byte, then it has a value - it can't be "blank", right?

And what's the purpose of that "first byte" (or is it the column?) being "blank"? Is that some trick to make FOCUS think it's looking at a master file when it's actually a data file?
Or is it going to be used in an unconstrained join by creating a dummy-column to apply a constraint to (a trick I am familiar with)? Am I even close?


That's about the point where my gray matter screams "DOES NOT COMPUTE" and I have to give up. Which is too bad, as I almost reached the end of the article. I do think I understand the concept of how it works, but I have only a vague idea of how it's achieved. Let alone I'd be able to apply it to the problem I started this thread for.

...Maybe if I take a magnesium wheel off that wheel-chair in the corner and set it on fire to make the table explode, the parts will just happen to fall in the right place?

Start here

You will need to define an alpha representation for any numeric fields involved (decorated with commas, currency sign, ...).
Create the utility file, and Join your matched HOLD to it so that there is a series of Seq value instances for each source record.
Define the FieldName as a Decode of the sequence number;
Define the Old- and NewFieldValue/Ann via nested IF THEN ELSE:

IF Seq EQ 1 THEN (alpha representation of field 1)
ELSE
IF Seq EQ 2 THEN (same for 2)
etc

and report.

I agree that this ancient article in question is lacking.

If you're still having trouble getting your arms around the concept, watch the recent MacGyver Webinar at MacGyver Technique. Using the CAR database to trial and error what I learned from the Webinar made it come together for me. Once understood, you'll have another tool in your aresenal.

OK, no MacGyver here.
Maybe (if I understood the problem) the example below is covering your question.

-Fred-

 
SET PAGE     = NOPAGE
SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY

-*--- Get 1 record with original data
TABLE FILE CAR
  SUM
    COMPUTE KEY/I2 = 1;
    COUNTRY
    CAR
    MODEL
    BODYTYPE
    COMPUTE DEALER_COST/D7S = 0;
    RETAIL_COST
  WHERE MODEL EQ 'V12XKE AUTO';
  ON TABLE HOLD AS HLDLEFT FORMAT ALPHA
END
-RUN


-* --- Get same record with some differences
DEFINE FILE CAR
  XCOUNTRY/A12 = 'UK';
  XMODEL/A24   = 'V12 XKE AUTO';
END


TABLE FILE CAR
  SUM
    COMPUTE KEY/I2 = 1;
    XCOUNTRY   AS COUNTRY
    CAR
    XMODEL     AS MODEL
    COMPUTE BODYTYPE/A12 = '';
    DEALER_COST
    RETAIL_COST
  WHERE MODEL EQ 'V12XKE AUTO';
  ON TABLE HOLD AS HLDRIGHT FORMAT ALPHA
END
-RUN


-* --- Match them to 1 single record
MATCH
  FILE HLDLEFT
    SUM
      DEALER_COST  AS DC1
      RETAIL_COST  AS RC1
    BY KEY
    BY COUNTRY     AS COUNTRY1
    BY CAR         AS CAR1
    BY MODEL       AS MODEL1
    BY BODYTYPE    AS BODYTYPE1
RUN
  FILE HLDRIGHT
    SUM
      DEALER_COST  AS DC2
      RETAIL_COST  AS RC2
    BY KEY
    BY COUNTRY     AS COUNTRY2
    BY CAR         AS CAR2
    BY MODEL       AS MODEL2
    BY BODYTYPE    AS BODYTYPE2
  AFTER MATCH HOLD AS HLDTOTAL OLD-AND-NEW
END


-*-- For each column, define a FLAG field
DEFINE FILE HLDTOTAL
  FLAG1/A1 = IF COUNTRY1   EQ COUNTRY2   THEN 'Y' ELSE 'N';
  FLAG2/A1 = IF CAR1       EQ CAR2       THEN 'Y' ELSE 'N';
  FLAG3/A1 = IF MODEL1     EQ MODEL2     THEN 'Y' ELSE 'N';
  FLAG4/A1 = IF BODYTYPE1  EQ BODYTYPE2  THEN 'Y' ELSE 'N';
  FLAG5/A1 = IF RC1        EQ RC2        THEN 'Y' ELSE 'N';
  FLAG6/A1 = IF DC1        EQ DC2        THEN 'Y' ELSE 'N';
END


TABLE FILE HLDTOTAL
  PRINT
    COUNTRY1    AS '' COUNTRY2     AS '' FLAG1 AS '' OVER
    CAR1        AS '' CAR2         AS '' FLAG2 AS '' OVER
    MODEL1      AS '' MODEL2       AS '' FLAG3 AS '' OVER
    BODYTYPE1   AS '' BODYTYPE2    AS '' FLAG4 AS '' OVER
    RC1         AS '' RC2          AS '' FLAG5 AS '' OVER
    DC1         AS '' DC2          AS '' FLAG6 AS ''


    BY KEY NOPRINT
    ON KEY SUBHEAD
    "Record1  <+0> Record2 <+0> Same"

    ON TABLE SET STYLE *
    TYPE=SUBHEAD, HEADALIGN=BODY ,$
    TYPE=DATA, COLUMN=COUNTRY1   ,COLOR=RED , WHEN=FLAG1 EQ 'N' ,$
    TYPE=DATA, COLUMN=COUNTRY2   ,COLOR=RED , WHEN=FLAG1 EQ 'N' ,$

    TYPE=DATA, COLUMN=CAR1       ,COLOR=RED , WHEN=FLAG2 EQ 'N' ,$
    TYPE=DATA, COLUMN=CAR2       ,COLOR=RED , WHEN=FLAG2 EQ 'N' ,$

    TYPE=DATA, COLUMN=MODEL1     ,COLOR=RED , WHEN=FLAG3 EQ 'N' ,$
    TYPE=DATA, COLUMN=MODEL2     ,COLOR=RED , WHEN=FLAG3 EQ 'N' ,$

    TYPE=DATA, COLUMN=BODYTYPE1  ,COLOR=RED , WHEN=FLAG4 EQ 'N' ,$
    TYPE=DATA, COLUMN=BODYTYPE2  ,COLOR=RED , WHEN=FLAG4 EQ 'N' ,$

    TYPE=DATA, COLUMN=RC1        ,COLOR=RED , WHEN=FLAG5 EQ 'N' ,$
    TYPE=DATA, COLUMN=RC2        ,COLOR=RED , WHEN=FLAG5 EQ 'N' ,$

    TYPE=DATA, COLUMN=DC1        ,COLOR=RED , WHEN=FLAG6 EQ 'N' ,$
    TYPE=DATA, COLUMN=DC2        ,COLOR=RED , WHEN=FLAG6 EQ 'N' ,$
END

Thanks for the links guys.

I got the MakeSeq script from j.gross'es link and created a version that adds a column with a sequence of characters (of course with the default start value increased to something useful). Seems useful for those A-B-C-..-Z link lists where some characters (typically 'Q') don't show up if you're just listing the first character of a bunch of names that you happen to have in your database. Now I can generate an alphabet to join against

Doh, of course I could have just added a few flag fields to track the differences. Good point. I guess I got too focused on trying to apply the style directly to the columns.

To document the solution a little more; I finally understand what those WITH statements mean! Here's my explanation of how it works, correct me if I'm wrong

In a JOIN, they're used to refer to a DEFINE'd field after the join is created (it needs to be, as apparently a JOIN resets all DEFINE's). The WITH statement itself has little to do with the JOIN though - what it does is point to a column in the same segment as where the DEFINE'd virtual column will be.

For example:

JOIN foo WITH bodytype IN CAR TO ...

This means there will be a DEFINE'd virtual column FOO in the CAR table in the same segment that BODYTYPE is in.

In a similar vein, we can specify in which segment we want to DEFINE a field:

DEFINE FILE CAR
    foo/A4 WITH bodytype = 'Foo';
...

This means we define a field FOO in the same segment that BODYTYPE is in.

These two uses of WITH combined make sure that the field we DEFINE'd is in the same place when the join is looking ahead for the field and when we declare the field.

JOIN foo WITH bodytype IN CAR TO ...
DEFINE FILE CAR
    foo/A4 WITH bodytype = 'Foo';
...

What escapes me is why we have to reference a field in a segment instead of just specifying the segment.

quote:

What escapes me is why we have to reference a field in a segment instead of just specifying the segment.

Bear in mind, the original JOIN syntax is for an equi-join: that requires a field on both sides of the join. Granted, in the present case (by construction) the From side is a constant and the To side has only one instance, but the JOIN engine needs two fields to compare for equality.

With the advent of condition-based joins, we could dispense with the root segment of the utility file and specify a Cartesian product (no WHERE clause); but there are navigational efficiencies gained by retaining the present structure.

I fail to see the relevance of the join syntax requiring fields in this case. Of course it does, but that's not the issue here or is it?

As far as I understand - referring to the example I gave earlier - there's no other use for the "bodytype" column in the join than to specify in which segment the virtual column "foo" (from the subsequent DEFINE) is to be found.

The join should be happening using the result of the expression "foo" from the DEFINE, not the column ("bodytype") that was specified in the WITH part. That would yield wrong results, and it doesn't.

My question really is about the WITH-syntax, which appears (to me at least) a little convoluted. It seems it should be possible to just specify the segment directly, instead of specifying a column in the segment to use; A direct reference instead of an indirect one.

The use of fieldname rather than segment name is because most people know the fieldnames, not many know the segment names. This is an end-user language after all.

The WITH in the JOIN and the WITH in the DEFINE have 2 different tasks, though at times that appears not to be the case.

Try and understand how this code fits together. You can try variations on a theme to see what changes happen.

JOIN 
        NEWCOUNTRY WITH CAR      IN CAR 
-*        NEWCOUNTRY WITH COUNTRY  IN CAR 
  TO 
        COUNTRY                  IN CAR TAG B
END
DEFINE FILE CAR
NEWCOUNTRY/A10 = COUNTRY;
CNTR1/I4 WITH COUNTRY = CNTR1+1;
CNTR2/I4 WITH CAR = CNTR2+1;
CNTR3/I4 WITH MODEL = CNTR3+1;
END
TABLE FILE CAR
PRINT COUNTRY CNTR1
      CAR CNTR2
      MODEL CNTR3
    B.CAR
-*    B.COUNTRY
END

quote:

My question really is about the WITH-syntax

Agreed, "WITH fieldname", as opposed to "WITH segname", was an arbitrary choice.

But note that, in a second or subsequent JOIN, segment names may not be unique, especially if the files were produced by HOLD. (You can control the fieldnames in a HOLD file, but not the segnames.) Also, in many cases the specified fieldname will be involved in the define, (and hence the define will be triggered to reevaluate when a new instance of the field is reached), so WITH fieldname is a natural.

Of course, the designers could have allowed a choice of fieldname or segname.

This message has been edited. Last edited by: j.gross,

Quote:
"This is an end-user language after all."

Clearly you haven't met my end users.

Example: My instruction to an end user wo has been using Windows and Lotus Notes for 15 years:

Me: "In your Lotus Notes, Click File, then Open ..... "
User: "I don't see the file on my desktop anywhere ..... "

quote:

I don't see the file on my desktop anywhere

Do they also have there automatic drinks tray on there PC.... The Eject button.