what's sql's distinct for FM???

[fcdm98]

I wish to make a query that shows me all the brand that a shop as, and of course I want the brand to appear once.

I have a database that contains the name of the shops, an other one with the products (prodNum, prodName, brand) and the last one is the association of the two first databases (to_have).

In sql the query would be: select distinct p.brand

from (product p inner join to_have h

on p.numProd = h.numProd)

where h.shopName = [user's choice];

Thanks for your help.

[John Caballero]

Hi there,

There isn't an equivalent for Distinct in FileMaker. There are some ways that that can be simulated, but they are somewhat involved. Maybe some of the big potatoes here can come up with a work-around. For those of you who don't know, Distinct in a SQL query returns one each of duplicate values. You can easily find duplicates in FM, but it will return all the duplicates whereas Distinct returns one of each duplicate.

[Ugo DI LUCA]

There are mutliple ways this can be done in FM, and depending on your structure, this can happen to bas as simple as complicated.

The quicker, IMO, would be :

- a global gIDsPasted

- a relationship R_ShowOnlyOne = gIDs::ProductID

- a SelfJoin on the BrandName

- a Calculation cDisplayOnce = Case(ProductID=SJonBrand:ProductID, ProductID,"")

and place this lates calc in one layout

- a List view with only the Brand field on it, or a portal using gIDs::ProductID

Then a script

ShowAllRecords

GoToLayou[where cDsiplayOnce is]

CopyAllRecords

GoToLayout[where gPastedIDs is]

Paste[select, gPastedIds]

GoToRelated Records ["R_ShowOnlyOnce"-show only]

GoToList View

or remove the last step and move to a layout with a portal with R_ShowOnlyOnce relationship and BrandField in it.

Now, you may also adapt your Line Item, so that any brand created is a distinct record. Then, later, you may use these sole keys for this unique purpose or disply your Items lists in some dynamic ways.

I personnally use this technique which allows me to display my products, or my line items in a kind of Tree display...

HTH

[-Queue-]

If you use a calculation field such as serial = self_transaction::serial, (replacing 'transaction' with a self_relationship based on the field that can be duplicated) then you can do a find for all records where this calculation is equal to one. This will only find one record for each possible duplicate, similar to SQL's 'distinct'.

[Mariano Peterson]

The DISTINCT clause in SQL actually returns one row for each different value within a column. It doesn't show one record for each duplicate, but rather shows each value exactly once, regardless of whether it is a duplicated value or not. If the value happens to be duplicated, the value is still only returned once. If the value is not duplicated and exists once, it is still returned once.

One very easy way to view the distinct values in a FileMaker field is to click into the field on a layout, then click <ctrl + i> (Note that this only works if the field is indexable). This will produce a floating window with a list of distinct values for that field, for all rows in the table (not just the current found set).

[Ugo DI LUCA]

Mariano, ctrl i doesn't produce anything here with a Macintosh version of FM6.

Queue, your calc doesn't really differ from mine, excepts it could take a bit long to search this field as it is unstored...

On the other hand, the Copy All records would be limited by FM current limit of 64,000 K

Waiting for some enlightment on Mariano's option...

Thanks

[Mariano Peterson]

Hmm... try right clicking (ctrl+clicking) in the field, then choose "Insert >", then choose "From Index...". This should open a window titled "View Index".

[Mariano Peterson]

Maybe the shortcut on a Mac is <apple + i>.

Also, from ScriptMaker you can use the "Insert from Index" script step to pop up the same "View Index" window.

[Mariano Peterson]

Another way to do this is to create a value list (lets call it "distinct_myField"), based on the option "Use values from field: 'myField'".

Then create a calculation field that returns text and is named "distinct_myField". The calc should be:

valueListItems( status(currentFileName), "distinct_myField")

This will display all the unique values in the field in the contents of distinct_myField.

[Lee Smith]

Hi Ugo,

Cmd I on my Mac will "Insert Index". Try putting your cursor in a field and look at Menu - Insert - Insert Index

Lee

[-Queue-]

Mariano Peterson said:

The DISTINCT clause in SQL actually returns one row for each different value within a column. It doesn't show one record for each duplicate, but rather shows each value exactly once, regardless of whether it is a duplicated value or not. If the value happens to be duplicated, the value is still only returned once. If the value is not duplicated and exists once, it is still returned once.

Yes, I know this. That's what my calc does, too. Simple semantic confusion... "This will only find one record for each possible duplicate". Change 'duplicate' to 'value'. I'm on pain meds, so you're lucky I got even that much right.

I fail to see how inserting from index provides a workable solution for the problem, though. ValueListItems would work, as long as the character limit isn't exceeded. Either way, I believe we've overlooked the main problem, which is pulling only the values related to a particular shop. So we'd need to base the value list on 'only related values' to a shop's id.

Now I'm wondering if a simple GTRR would suffice or a portal displaying the related brands.

[John Caballero]

Mariano Peterson said:

The DISTINCT clause in SQL actually returns one row for each different value within a column. It doesn't show one record for each duplicate, but rather shows each value exactly once, regardless of whether it is a duplicated value or not. If the value happens to be duplicated, the value is still only returned once. If the value is not duplicated and exists once, it is still returned once.

Thanks for providing that DISTINCTion, Marz!

[fcdm98]

well well, thanks for your help!

I'm going to try all of this.