Comparing two fields and their data.

[Brett Force]

I would appreciate some help on this problem I'm having.
Problem: I have two text fields that hold nearly 800 hundred numbers each
and are displayed line by line. What I want to do is compare the two fields
and output any numbers that do not exist in (both) fields at the same time
to a "report field". Basically, I'm trying to update a V12 DB using LINGO
but have not been succsessful at creating a script that keeps duplicates
from being inserted. My solution: Load the two separate data sets into
director fields and then test to see which numbers do not exist in both
which will only be 39 entries as it happens. Once I know what the entries
are I can edit an EXCEL file and update my V12. Not pretty, but it's all I
can come up with and I'm not well versed in the use of lists which I believe
is what is needed here.

I would rather know how to use native V12 scripting to do this but have not
received a reply from integration new media's V12-L forum and this is
driving me crazy. Thanks.

[Rob Dillon]

Hi Brett,

If you only want to compare line X of one field with line X of another
field then that's fairly simple. It will be slow, but simple.

On compareFields fieldOneName,fieldTwoName
oddList = []
thisManyLines = member(fieldOneName).line.count
repeat with i = 1 to thisManyLines
if member(fieldOneName).line[i] <> member(fieldTwoName).line[i] then
oddList.add(i)
end if
end repeat
return oddList
end

Be aware that if you are going to do this then you will be comparing
text strings and so space charactors count. With text "7" does not
equal " 7" or "7 ".

Do you get the contents of the fields as a result of some database
operation or are you placing the numbers into a field yourself? If the
numbers are already in the fields then you could use a list function
like this:

on compareFieldContents fieldOneName,fieldTwoName
tempList1 = []
tempList2 = []
oddList = []
thisManyLines = member(fieldOneName).line.count
repeat with i = 1 to thisManyLines
tempList1[i] = integer(member(fieldOneName).line[i])
tempList2[i] = integer(member(fieldTwoName).line[i])
end repeat
repeat with j = 1 to thisManyLines
if tempList1[i] <> tempList2[i] then
oddList.add(i)
end if
end repeat
tempList1 = []
tempList2 = []
return oddList
end

[Brett Force]

Thanks, but your solutions are referencing by line position. If line[1] <>
line[1] does not work. I need to test to see if line[1] <> line[3] Both
fields have 800 of the same values each and only one field has the
additional 35 values or 835. The numbers are not sequential in the way of
1,2,3,4,5,6. Instead they are 1,4,6,9,10 so comparing by line is returning
faulty results.
"Rob Dillon" <rob@nospam-ddg-designs.com> wrote in message
news:101220031452496682%rob@nospam-ddg-designs.com...

> Hi Brett,
>
> If you only want to compare line X of one field with line X of another
> field then that's fairly simple. It will be slow, but simple.
>
> On compareFields fieldOneName,fieldTwoName
> oddList = []
> thisManyLines = member(fieldOneName).line.count
> repeat with i = 1 to thisManyLines
> if member(fieldOneName).line[i] <> member(fieldTwoName).line[i] then
> oddList.add(i)
> end if
> end repeat
> return oddList
> end
>
> Be aware that if you are going to do this then you will be comparing
> text strings and so space charactors count. With text "7" does not
> equal " 7" or "7 ".
>
> Do you get the contents of the fields as a result of some database
> operation or are you placing the numbers into a field yourself? If the
> numbers are already in the fields then you could use a list function
> like this:
>
> on compareFieldContents fieldOneName,fieldTwoName
> tempList1 = []
> tempList2 = []
> oddList = []
> thisManyLines = member(fieldOneName).line.count
> repeat with i = 1 to thisManyLines
> tempList1[i] = integer(member(fieldOneName).line[i])
> tempList2[i] = integer(member(fieldTwoName).line[i])
> end repeat
> repeat with j = 1 to thisManyLines
> if tempList1[i] <> tempList2[i] then
> oddList.add(i)
> end if
> end repeat
> tempList1 = []
> tempList2 = []
> return oddList
> end

[Rob Dillon]

If you know which lines are potentially different then you can compare
only those lines. Do you know which lines are potentially different,
or, are these differences placed randomly in the second field?

--
Rob
_______
Rob Dillon
Team Macromedia
[url]http://www.ddg-designs.com[/url]
412-243-9119

[url]http://www.macromedia.com/software/trial/[/url]

[Brett Force]

They are placed randomly in the field. What happens is an exhibitor manager
updates our exhibitor database via our Intranet. I export an Excel file on a
regular basis to update my V12 DB which is being used for a CDROM project.
If I try to update the V12 with the Excel file, I end up with the new
records but also duplicates of the records I've previously imported. I
finally received a response from INM and they said that this could be
prevented by using indexes. The rip is I do have the ExhibitorName field in
my V12 indexed and full-indexed but still get the dupes. So what I'm trying
to acomplish is picking out which booth numbers are newly added. I thought
this could be accomplished by importing booth numbers from my existing V12
in one text field and then imorting the new list into another text field and
finally comparing the two field to see which numbers do not exist in both
fields at the same time. This would return the booth numbers that are new.
Hope I have'nt confused more.


"Rob Dillon" <rob@ddg-designs.com> wrote in message
news:111220030852471014%rob@ddg-designs.com...

> If you know which lines are potentially different then you can compare
> only those lines. Do you know which lines are potentially different,
> or, are these differences placed randomly in the second field?
>
> --
> Rob
> _______
> Rob Dillon
> Team Macromedia
> [url]http://www.ddg-designs.com[/url]
> 412-243-9119
>
> [url]http://www.macromedia.com/software/trial/[/url]

[Rob Dillon]

No, that makes sense. If you can import the booth numbers directly into
a list the process can be pretty quick. If you have to use text then it
will be slower. Here's the field version:

on compareFieldContents fieldOneName,fieldTwoName
-- fieldTwoName is longer of the two...
tempList1 = []
tempList2 = []
oddList = []
thisManyLines = member(fieldOneName).line.count
repeat with i = 1 to thisManyLines
tempList1[i] = integer(member(fieldOneName).line[i])
end repeat
thisManyLines = member(fieldTwoName).line.count
repeat with i = 1 to thisManyLines
tempList2[i] = integer(member(fieldTwoName).line[i])
end repeat
repeat with j thisManyLines down to 1
if getOne(tempList1,tempList2[i]) then
tempList2.deleteAt(i)
end if
end repeat
tempList1 = []
return tempList2
end

Make two lists from the fields. The new booth numbers should be in
fieldTwoName. Then use the getOne list function to find each booth
number from the first list in the second list. If it's there, delete
it. You'll be left with the short list of new booth numbers.

--
Rob
_______
Rob Dillon
Team Macromedia
[url]http://www.ddg-designs.com[/url]
412-243-9119

[url]http://www.macromedia.com/software/trial/[/url]

[Brett Force]

Thanks for the script. It worked but I hade to modify it a bit because I was
getting an index out of range error. I added item[1] to both tempList
variables and changed the i variable to j in the last repeat loop. Here is
the edited script. Thanks for puttiing me on the path. Now that I look at
the script it makes perfect sense but I don't have a good knowledge of using
lists.

on compareFieldContents fieldOneName,fieldTwoName
-- fieldTwoName is longer of the two...
fieldOneName = "bTable"
fieldTwoName = "zTable"
tempList1 = []
tempList2 = []
oddList = []
thisManyLines = member(fieldOneName).line.count
repeat with i = 1 to thisManyLines
tempList1[i] = integer(member(fieldOneName).line[i].item[1])
end repeat
thisManyLines = member(fieldTwoName).line.count
repeat with i = 1 to thisManyLines
tempList2[i] = integer(member(fieldTwoName).line[i].item[1])
end repeat
repeat with j = thisManyLines down to 1
if getOne(tempList1,tempList2[j]) then
tempList2.deleteAt(j)
end if
end repeat
tempList1 = []
return tempList2
end
"Rob Dillon" <rob@ddg-designs.com> wrote in message
news:111220031231423344%rob@ddg-designs.com...

> No, that makes sense. If you can import the booth numbers directly into
> a list the process can be pretty quick. If you have to use text then it
> will be slower. Here's the field version:
>
> on compareFieldContents fieldOneName,fieldTwoName
> -- fieldTwoName is longer of the two...
> tempList1 = []
> tempList2 = []
> oddList = []
> thisManyLines = member(fieldOneName).line.count
> repeat with i = 1 to thisManyLines
> tempList1[i] = integer(member(fieldOneName).line[i])
> end repeat
> thisManyLines = member(fieldTwoName).line.count
> repeat with i = 1 to thisManyLines
> tempList2[i] = integer(member(fieldTwoName).line[i])
> end repeat
> repeat with j thisManyLines down to 1
> if getOne(tempList1,tempList2[i]) then
> tempList2.deleteAt(i)
> end if
> end repeat
> tempList1 = []
> return tempList2
> end
>
> Make two lists from the fields. The new booth numbers should be in
> fieldTwoName. Then use the getOne list function to find each booth
> number from the first list in the second list. If it's there, delete
> it. You'll be left with the short list of new booth numbers.
>
> --
> Rob
> _______
> Rob Dillon
> Team Macromedia
> [url]http://www.ddg-designs.com[/url]
> 412-243-9119
>
> [url]http://www.macromedia.com/software/trial/[/url]