Jump to content

Filtering records from 2 lists

This topic is 5702 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!!

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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


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. :wink2:


Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ...

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

This topic is 5702 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.