Omit record with specific substring

[renev]

Hello,

How to omit records using script which does not match particular sub string?

For example if I have strings "JUMPER WIRE,0.6Dx12.5MM" , "JUMPER WIRE,1.8DX33MM,ADD TUBE" ,"JUMPER WIRE,D2.0,COPPER_02,BALM"

I want to Omit records which does not match "JUMPER WIRE" and "1.8D"

Thanks in advance

[mr_vodka]

Seems to me that these pieces of information should be in seperate fields rather than 1.

[comment]

I agree, but it's not always feasible with manufacturers' descriptions. Anyway, I believe a search request for:

"JUMPER WIRE" * "1.8D"

should find/omit all records that begin with "JUMPER WIRE" and also contain the string "1.8D".

[renev]

Thanks for the suggestion.

But here the problem is I need to select the items to calculate BOM cost.

The strings I have given earlier are item descriptions.

I already have 2 search requests, model name and revision. I want to put up another condition such that items with item description other than "JUMPER WIRE"*"1.8D" are to be omitted.

So how can i put a not condition here? Any thoughts?

[comment]

If you are doing this manually, then check the Omit box for this request. If it's scripted, then add a Omit Record step after creating this request.

[renev]

I'm sorry if I have not conveyed my problem properly.I tried the solution you have given but still all the items other than "JUMPER WIRE" "1.8D" also appear in my found set.

Following are my script steps

Go to Layout["BOM Data"]

Show All Records

Enter Find Mode[]

New Record/Request

Set Field[bOM Data::Model;"Test"]

Set Field[bOM Data::Revision;"Rev1"]

New Record/Request

Set Filed[bOM Data::Item Description;"≠"& "JUMPER WIRE"*"1.8D"

Omit Record

Perform Find[]

[mr_vodka]

Go to Layout ["BOM Data"]

Enter Find Mode[]

Set Field [bOM Data::Model;"Test"]

Set Field [bOM Data::Revision;"Rev1"]

New Record/Request

Set Field [bOM Data::Item Description; "JUMPER WIRE"*"1.8D"]

Omit Record

Perform Find[]

[comment]

First, there's no need to show all records before doing a find - it doesn't accomplish anything. Also, when you enter Find mode, you already have one find request.

Now, your criteria for the last request need to be exactly as they appear above, including the quotation marks, and excluding everything else (where did you come up with the "≠" thing?). One way to do this is to paste the following into the Set Field's calculated result window:

""JUMPER WIRE" * "1.8D""

so your script looks like this:

Go to Layout["BOM Data"]

Enter Find Mode[]

Set Field [ BOM Data::Model;"Test" ]

Set Field [ BOM Data::Revision;"Rev1" ]

New Record/Request

Set Field [ BOM Data::Item Description;; ""JUMPER WIRE" * "1.8D" " ]

Omit Record

Perform Find []

If, as it seems, you are hard-coding your criteria, you could do all of this simply by:

Go to Layout["BOM Data"]

Perform Find [ Restore ]

and just specify your requests within the Perform Find[] step.

[renev]

But that is what i dont want to omit.That was the reason I put # condition there.

I want to keep records with description JUMPER 1.8D and omit other records say JUMPER D2.0, JUMPER 0.8D etc.

I can't just add a new request with JUMPER 1.8D because there are other items with description other than JUMPER for this BOM.

Hope I'm clear this time.

[comment]

No, it's not. Why can't you look for items that are Test, Rev1 and JUMPER 1.8D - all in the same single request?

[renev]

The query resulted in zero records

Okay I will explain my problem again.

We get BOMs from factory which are imported to our database.They contain many parts which we dont have to consider for our BOM calculation.

Say we have some parts with the description for a bom

SCW,M3X0.5P,P_SFW,L=10MM

SCW,M3X0.5P,P_SFW,L=12MM

JUMPER WIRE,0.6Dx7.5MM

JUMPER WIRE,0.6Dx12.5MM

JUMPER WIRE,0.6Dx27.5MM

RES,CR,1K-OHM,5%,1206

DIO SW,100V,0.5W,DO-35,MOLDED GLASS

JUMPER WIRE,1.8DX26MM,ADD TUBE

I want following parts to be the result of my query

SCW,M3X0.5P,P_SFW,L=10MM

SCW,M3X0.5P,P_SFW,L=12MM

RES,CR,1K-OHM,5%,1206

DIO SW,100V,0.5W,DO-35,MOLDED GLASS

JUMPER WIRE,1.8DX26MM,ADD TUBE

Jumper wire other than 1.8D are manufactred in house so we dont have to list those items in our BOM.

Can this be done using a single search command or do i need to navigate record by record in the found set?

Thanks for the help anyways. I really appreciate it.

[comment]

This is still not very clear, but I am going to take a guess: you want to find records that are Test AND Rev1, but not Jumper - unless the Jumper happens to be 1.8D?

If so, I believe you can do it this way:

Go to Layout["BOM Data"]

Enter Find Mode[]

Set Field [ BOM Data::Model;"Test" ]

Set Field [ BOM Data::Revision;"Rev1" ]

#

New Record/Request

Set Field [ BOM Data::Item Description;; "JUMPER WIRE" ]

Omit Record

#

New Record/Request

Set Field [ BOM Data::Model;"Test" ]

Set Field [ BOM Data::Revision;"Rev1" ]

Set Field [ BOM Data::Item Description;; ""JUMPER WIRE" * "1.8D" " ]

#

Perform Find []

Note that the "*" wild card needs to be surrounded by spaces.

[renev]

Wow..great..

That exactly what I wanted.

Thank you soo much for the help.

Could you please explain me the script a bit..

In first three steps you are omitting the records with JUMPER WIRE..

In the second three steps you are find the records only with JUMPER WIRE 1.8D..

My confusion is how did the union work here?

[comment]

I think the key to understanding how it works is to compare the script with the plain English specification above:

Find records that are Test AND Rev1, but not Jumper - unless the Jumper happens to be 1.8D

In logical terms, that translates to:

Model="Test" AND Revision="Rev1" AND NOT (Description="JUMPER WIRE")

OR

Model="Test" AND Revision="Rev1" AND Description="JUMPER WIRE" * "1.8D"

Translated to Filemaker requests:

1. Find: Model="Test" AND Revision="Rev1"

2. Omit: Description="JUMPER WIRE"

3. Find: Model="Test" AND Revision="Rev1" AND Description="JUMPER WIRE" * "1.8D"

[renev]

I can not say I understood fully.. )

Anyways thank you so much for the help.