Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

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


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

Recommended Posts

  • Newbies
Posted

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.

Posted

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.

Posted

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

Posted

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

Posted

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

Posted

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

Posted

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.

Posted

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

Posted

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. grin.gif 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. wink.gif

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.

Posted

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!

wink.gif

This topic is 7577 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.