So I have a database that I use to order 'scans' from an outside vendor. Each scan has a unique name and also a 'scan length' in frames. I receive from the vendor a text file showing what they've completed (which shows scan name and scan length). I have imported this into a new table.

What I want to do is compare the two fields and if they match have the field (or a separate field) turn a colour and if they don't match it turns a different colour.

example

Scan Ordered:

[color:blue]Name / Length

ml_076_010_pl01_v01 / 56

ml_076_020_pl01_v01 / 45

Scan delivered:

[color:blue]Name/Length/Colour

[color:blue]ml_076_010_pl01_v01 / [color:green]56 / [color:green]Green - Matching scan

[color:blue]ml_076_020_pl01_v01 / [color:red]44 / [color:red]Red - Non matching scan

I've tried a couple of calcs but don't seem to be getting anywhere. Do I need globals of the colours?

Thanks in advance for any help,

Ian

No, you don't need globals. You need a relationship between the tables, based on matching fields (both of them). Then you could use a Conditional Formatting (which is just a layout thing, right-click to apply it) to turn it a different color. It will only do 1 color, but I don't see any point in the red anyway, unless you have some other field, such as a date, which allows you to say when something is "overdue."

What I would do is to run a Script, to set a Date Received field, and use that to show what's received. Because it would be about 100 times faster, to work with the data later; it could be searched for "not received." You could run it within an "Import Scans" script (after the import -). You would be on a layout of your Scans.

Freeze Window

Go To Record/Request [First]

Loop

    If [ not IsEmpty ( relationship::field in Imported Scans table ) ]

        Set Field [ Date_rcvd; Get (CurrentDate) ]

    End If

    Go To Record/Request [Next, [x] Exit after last ]

End Loop

Commit Record [ [x] No dialog ]

I am not sure if you has FM9 for conditional formatting, since his profile states 8.5