Jump to content
Sign in to follow this  

Checking what I receive against what I ordered!

Recommended Posts

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.


Scan Ordered:

[color:blue]Name / Length

ml_076_010_pl01_v01 / 56

ml_076_020_pl01_v01 / 45

Scan delivered:


[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,


Share this post

Link to post
Share on other sites

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]


    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 ]

Share this post

Link to post
Share on other sites

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

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.