Jump to content
Sign in to follow this  
Sleepy1972

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.

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

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]

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 ]

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

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