Link two frields by Like/Match in expression or script

[darren_dixon]

Hi,

I need to match/like two fields, Order Number and Job ID by the Job ID.

This can be in either an expression or in the script. I would like to use this in a straight table.

The Order Number field has GEN- at the front.

The Job ID is 3 letters and 7 numbers

The Order Number has 2 numbers and one letter at the end.

Example data:

Order Number:                    Job ID:

GEN-HSG2024471000P       HSG2024471

GEN-HSG2024472000C       HSG2024472

GEN-HSG2024473000G       HSG2024473

GEN-HSG2024474000B       HSG2024474

I had tried the following but did not work.

if([Order Number] LIKE [Job ID], [Order Number], "NULL")

if([Order Number] LIKE *[Job ID]*, [Order Number], "NULL")

Thanks for your help.

Thanks,

Darren

[swuehl · Accepted solution]

This might work:

if([Order Number] LIKE '*'& [Job ID] & '*', [Order Number], 'NULL')

[darren_dixon]

Thank you for your quick response.

Works great!

[darren_dixon]

I've tested this in a straight table and works great.

The Order Number is in one table and Job ID is in another.

How would I join these two in a script to create a relationship?

Thanks,

Darren

[swuehl]

How are these two tables linked to each other (by which key field)?

You can also try

mid([Order Number],5,10) as [Job ID]

in the load of your order Table, if the Number layout is always embedded into order number like this.

Then your tables will be linked by Job ID.

[darren_dixon]

The two tables are below.

The join between the two tables would be the Order Number and Job ID

Repairs Costs;

     LOAD [Job ID],

     [V Labour Cost],

     [V Material Cost],

     [V Plant Cost],

     [V Sub Contract Cost],

     [V Total Cost]

FROM

[..\..\..\..\Responsive Repairs\Repairs_Job Costs.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq, header is 14);

Handheld:

     LOAD "Contract",

     OrderNumber AS "Order Number",

SQL SELECT *

FROM handheld.dbo."tblXWR_Appointments";

[swuehl]

So have you tried:

Handheld:

     LOAD "Contract",

     mid([OrderNumber],5,10) as [Job ID],

     OrderNumber AS "Order Number";

SQL SELECT *

FROM handheld.dbo."tblXWR_Appointments";

which should link your two tables?

[darren_dixon]

That's worked great.

Thank you for your help and support.

Darren