Hi All,

I was hoping someone could give me a hand with a procedure.

Suppose I have two tables I am working with. Table1 has the fields Year, Make and Model and Table2 only has one field called YrMkMod. The data in Table2 can vary and can dirty. If the data in Table2 has relevant/matching information as Table1 I need to flag the records in Table1 using the Flag Field. As you can see in this example the data matches for the 2015 Dodge Challenger and the 2018 Dodge Charger but the data in Table2 is misarranged and "dirty" as I call it and can contain other data not relevant to Table1.

I was hoping someone could help me with the Script that would look at the records in Table2, find the matching records in Table1 and then modify the Flag Field to "Yes' as shown in the attached example. Any help would be greatly appreciated. 

As always thank you in advance for your help.

Hi Bolt-on-Trailers.

Suggest to use the "StringFind" function.

Here is an example of DQL program for solution.

define temp "Year model" text.

define temp "Mark" text.

define temp "Position" number.

define temp "Model" text.

define temp "counter" number.

for cars;

temp Year model : = Year.

temp Mark : = Mark.

temp Model : = Model.

temp Counter: = 0.

for Text;

temp Position : = StringFind (Text data, temp Year model).

if temp Position > 0 then temp Counter: = 1. else Counter: = 0. end

temp Position: = StringFind (Text data, temp Mark).

if temp Position > 0 then temp Counter: = temp Counter + 1. else Counter: = 0. end

temp Position: = StringFind (Text data, temp model).

if temp Position> 0 then temp Counter: = temp Counter + 1. else Counter: = 0. end

end

if temp counter = 3 then

modify records

In stock: = yes.

end

end

Thank you so much for your help. I will give this a try.

Hi All,

I managed to make some changes to the script but it stops with an error at the first "else" statement and I cant seem to resolve it. Anyone have ideas what I am doing wrong? Thanks again..

define temp "Year model" text.

define temp "Mark" text.

define temp "Position" number.

define temp "Model" text.

define temp "counter" number.

for Table1 ;

temp Year model := Year.

temp Mark := Mark.

temp Model := Model.

temp Counter := 0 .

for Table2 ;

temp Position := StringFind (Table1 Year, temp Year model) .

if temp Position > 0 then temp Counter := 1. else Counter := 0. end

temp Position := StringFind (Text data, temp Mark).

if temp Position > 0 then temp Counter := temp Counter + 1. else Counter: = 0. end

temp Position := StringFind (Text data, temp model).

if temp Position> 0 then temp Counter := temp Counter + 1. else Counter: = 0. end

end

if temp counter = 3 then

modify records

In stock: = yes.

end

end

"if temp Position > 0 then temp Counter := 1. else Counter := 0. end"

The problem is your . as end of statement.

. is also decimal separator so if you use if it is next to a number without a space between it and the number the parser will read it as decimal separator and not end of statement (line).

if temp Position > 0 then temp Counter := 1 . else Counter := 0 . end

should fix it.

Hi Again everyone,

I managed to get the script to run without any errors but it doesn’t seem to be flagging the records in Table1 as needed. Attached is a simple example of the data in both Table1 and Table2. As you can see Table1 has four fields and Table2 only has one field. My objective is for the script to look for matching records between the two tables and flag the records in Table1 that match records in Table2 by modifying the Flag field in Table1 := Yes.

Upon running the procedure the only two flagged “yes” records in Table1 would be the 2018 Dodge Charger and the 2015 Dodge Challenger because the data is a match between both tables.

I’m not sure what I am doing wrong. Maybe there is a better way and I’m just making it too complicated. Again any help would be appreciated and thanks again in advance for all your help.

define temp "Year model" text.

define temp "Mark" text.

define temp "Position" number.

define temp "Model" text.

define temp "counter" number.

for Table1 ;

temp Year model := Year.

temp Mark := Mark.

temp Model := Model.

temp Counter := 0 .

for Table2 ;

temp Position := StringFind (Table1 Year, temp Year model) .

if temp Position > 0 then temp Counter := 1 . else Counter := 0 . end

temp Position := StringFind ("Text data", temp Mark) .

if temp Position > 0 then temp Counter := temp Counter + 1 . else Counter := 0 . end

temp Position := StringFind ("Text data", temp model).

if temp Position> 0 then temp Counter := temp Counter + 1 . else Counter := 0 . end

end

if temp counter = 3 then

modify records

Flag := yes .

end

end

Good contributions here.

1. The biggest problem here is that you try to take a general example (read idea) and use it directly.

temp Position := StringFind (Table1 Year, temp Year model) . 

This one make no sense as you check the source table for itself. temp year is Table1 Year.

temp Position := StringFind ("Text data", temp Mark) .

Here you check if there is temp mark in the string "Text data" this will only be true if temp mark is either "Text" or "data" or any of the letters in this string. 

Understanding is everything in programming. You need to invest the time to understand the functions you use then it will be easy to make the program you want.

StringFind will look for a substring in a string. So StringFind("this is a test","test") will return the start position of "test" will stringfind ("This is a test","For") will return 0 .

Anyhow the DQL so kindly provided by Mr. Nerland might be a little excessive for what you need.

define "dirty" text .

for Table1 ;

dirty := "no" .

for table2

if stringfind(YrMkMod ,Table1 Year )>0 then

if stringfind(YrMkMod ,Table1 Make )>0 then

if stringfind(YrMkMod ,Table1 Model )>0 then

dirty := "yes" .

end

end

end

end

modify records

Flag := dirty .

end

Here we loop table 1 and then loop through all Table2 records for Year,Make,Model. Here we also address another issue with the previous DQL. It reset counter so its enough if the last record in Table2 is not a match and counter will be 0 as you don't have an overall flag for the run through.

Dirty is reset before each loop of table2 so if its gets yes on record1 it stay yes even thought the rest is no.

Then we simply modify the record in table1 with yes or no and hence you don't need to reset anything if things change as if things change you will get a yes or a no directly.

Good luck.

DE9 is soon her with many new challenges ;-)