Filtering records from 2 lists

[jeffmcg]

I have 2 lists of stocks

stocklist1: a,b,c,d

stocklist2: a,b,c,d,e,f,g,h

I want to eliminate the duplicates from list 2.

Then I want to sort the stocks that are left e,f,g,h.

The lists are actually very long.

Any ideas which way to start would be appreciated!!

[jeffmcg]

I have tried just about every script and value list and calculation i can think of.

Anybody got guidance?

[LaRetta]

Hi Jeff,

Give this a go ...

Create a relationship on that field that contains a, b, c etc as:

Stocklist1::thisField = Stocklist2::thisField.

From Stocklist1, perform the following script:

Go To Related [ Match only related records; Match found set; from Stocklist2; using Stocklist2 layout ]

Show Omitted

Sort [ as you need ]

LaRetta :wink2:

[jeffmcg]

My script looks like

Go to Related Record[show only related records;Match found set; From table: "SmallerList";]

Show Omitted Only

When i run it no records are found, then I run it again all records are found.

Thanks for your help!!!!

[jeffmcg]

Does it matter that my related fields are calculations?

[LaRetta]

You should be GTRRing to the LARGER list from the smaller. Notice I said be on layout from Stocklist1 and GTRR to Stocklist2.

Calculations are fine as long as the calculation on the LARGER side (child side) is indexable. What the script does: A GTRR is the same as a Find. So essentially, by GTRRing to the larger list, the resultant found set will be your smaller set. Then the Show Ommitted will display all records from the larger list that were NOT part of the GTRR found set.

[jeffmcg]

I changed GTRR, still has same problem. When I execute script now small list disapears then returns if i execute again.

[LaRetta]

You aren't providing enough information.

1) What is the calculation you are using and is it indexable? Since you've performed a GTRR on it, it will show 'indexed' when you view it in field definitions.

2) Did you start on a layout which is based upon StockList1? Check Layouts > Layout setup and make sure it is based upon StockList1.

3) Your GTRR must have BOTH checkboxes checked 'Show Related Records' and 'Match all records in set.'

4) Do you have a layout which is based upon StockList2? And is it the layout you specify in your GTRR step?

If you don't have a match, your script will not work. So here again the specific script, adding a test that the relationship is working:

Go to Layout [ any layout based upon StockList1 ]

If [ IsEmpty ( [color:red]StockList2::thisMatchFieldCalculation ) ]

Show Custom Dialog [ OK ; No records Found ]

Halt Script

Else

GTRR [ StockList2 ; using layout StockList2, Show Related Only, Match ALL records ]

Show Omitted

End If

It would help to understand what we are working with here. Are these two tables in the same file? Why do you have two 'almost' identical lists?

Follow this specifically and tell me what happens.

LaRetta

[jeffmcg]

with new script I get message no records found. Productlist islist1(smaller).

StockList is list 2 Larger.

They are 2 different fm files. I imported from excel into each from different sources. I defined a file reference into each. I have a graph on smalllist that shows many o many relationship to large list. I specified layout from larger list

[jeffmcg]

The calculation is

cSt_Sym CalcIndexed, =Trim(Substitute(St_sym ;"Equity";""))

[jeffmcg]

I just want to take a minute to thank you for your help

[LaRetta]

The 'No Records Found' means your relationship is not working. Can you attach your files? Have you verified that the result of that calc is the same in both files? :wink2:

[jeffmcg]

I have been looking back and I am unclear as to what to put in script here

If [ IsEmpty ( StockList2::thisMatchFiel dCalculation ) ]

[LaRetta]

I didn't know the name of the field you were using for your match. It would be the cSt_Sym CalcIndexed field.

So when you specify the If[] script-step and the calc box opens, select the table occurrence and that field. Again, I don't know the exact name of your larger list's table.

IsEmpty (largerlist::cSt_symCalcIndexed )

Edited February 14, 2007 by Guest

[jeffmcg]

How can I send you something to look at?

[LaRetta]

Zip your file(s) then create a reply. At the bottom is a link called Manage Files. It will ask you to attach your file. Browse for your file, then Add. When done, be sure to click Finish. Then save your post. :wink2:

[jeffmcg]

ok

[jeffmcg]

Another approach I am trying to take is putting a field with 1 on every record on smalllist.

Then i have a field on layout in big list that is supposed to show 1 from field from small list but it seems to put a 1 on first 500 names whether they match or not. Can I do a look up from field on layoutBiglist that shows name matching to smalllist?

[LaRetta]

You best best is to first find out why you can't get a match. My suggested method would save marking the records at all and that's why I suggested it - it is the simplest method. If you do a lookup from the smalllist but the match isn't working, the lookup won't work either. Your match field calculation isn't right or your relationship definition isn't right thus my suggestion to attach your file.

[LaRetta]

Upon re-read, I wonder if you're not getting a match because of case sensitivity.

cSt_Sym CalcIndexed, =Trim(Substitute(St_sym ;"Equity";""))

I still have no idea what kind of data is represented in the match field but if you are attempting to remove the word Equity, and the word is actually equity, it will NOT remove it because the Substitute() function is case-sensitive.

You might try using instead:

Trim( Substitute ( St_sym ;

[ "Equity";"" ] ;

[ "equity";"" ] ) )

And make sure the field is set to text result. It is certainly an indexable field ...

[jeffmcg]

Hi Laretta,

I did attach the file for you to look at. I got it to work by inserting a 1 field in small list, then doing a relookup field contents. then i formated my field on big list as boolean yes or no.

I have a different question now but it seems to be a similar concept.

I have a projects layout.

I enter a contact related to the project from a drop down valuelist that i created from a contacts table which is related.

Now I am trying to write a script to send an email to this contact.

How do i match the email address to this name?

[LaRetta]

You file attachment didn't work. If it had worked, it would show your attachment on the post underlined as a link for download.

As for email, it would be best to post that as a separate question in ScriptMaker with subject appropriately indicating you need email script. I won't make a suggestion on it because I haven't needed to do it since vs. 6. I only make suggestions on things I've done myself. :wink2: