Avatar of noad
noad
Flag for United States of America asked on

Sort Order

I have a layout that is set to show records of closed leads, when they show they are in the order that the lead/record was create. I made the dealer name / Contact name / city  and State into sort buttons they sort in descending order and the records are view from top to bottom.
The problem is that it if I have a company called ABD and another called Radio b/c it is sortin gin descending order the radio company name is on the top of the company name ABC.

AS I said before I have then sorting in descending order b/c that is the only way I found then to display in order from the top and not from the position that they are in on the layout.

How can I make it so that while keeping then in descending order I can have them display in alphabetical order.

Sort


ViewThe view below is what I see when I view the layout
FileMaker Pro

Avatar of undefined
Last Comment
noad

8/22/2022 - Mon
SOLUTION
Tom Grell

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
noad

ASKER
Yes your correct, it will put them in alphabetical order, but then it looks like this  ( see pic attached ) I need it to start from the top...

Any idea on how to fix this?ascending order
Tom Grell

This is just a guess, but do you have several blank records in the database? If so these would sort to the top before the "Buy Direct" record and force it down the page.
noad

ASKER
yes I do...

I see what you mean....

I have 2 layouts one called New Leads the other closed leads
I have a script that copies then I click on a button the new leads data to the closed leads layout. but there is always a closed leads that is open without any info.
That is why the space.

I'm guessing that my setup is not correct.

What would be the right way to do it?
I mean have the info from the New Leads layout copy to the closed leads layout?

Any idea?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Will Loving

I would have assumed that the Closed Leads were in a different table, in which case you shouldn't have any blank records. If you are copying the fields - Dealer, Name, Email, etc. - from one set of fields to another within the same record and then clearing the original fields, then the only way to get rid of the apparent blanks would be to do a scripted Find before the sort that Omits records with blank Closed Lead fields.

What's happening is that records with blank Dealer name are alphabetically higher than those with entries. The way to get those blank records to sort at the end - if you don't want to do the Find/Omit first - is to sort first by a "flag" field that indicates whether the records is Closed or not and set the sort order to put records that are Closed first followed by those that aren't. Then add the Dealer name as a secondary sort.

As I think I've noted before, it's always best to not duplicate data unnecessarily.  If you really have two sets of fields in the same record, e.g. Dealer_Open, Dealer_Closed, Name_Open, Name_Closed, etc., my recommendation would be to eliminate the extra set of fields and simply add a "Flag" field for Open/Closed. Display just one set of fields with the Open/Closed flag prominently displayed and possibly using Conditional formatting to highlight which is which. You can also add a Constrain script step to your scripts to Omit whichever set of records you don't want to see, Open or Closed.
noad

ASKER
How would I go about writing the script so that when I click on the Closed Leads tab and it goes into the layout it only shows the records that I want to see.
Should the script  have omit record date/name ect...???
Instead of sort?
Will Loving

If you want to see all Closed leads, just put a saved Find in the script that would produces those and then sort them. I think you have a field that indicates Closed right?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
noad

ASKER
yes I do
 
 the fileds end with _closed
Will Loving

Well, that can work simply doing a find with a asterisk * for any character, but what I meant was a 'flag' field that indicates Open or Closed, or a Timestamp field that indicates the day and time the record was Closed. You can search on any of those as well.
noad

ASKER
I see what you mean....

Question...
If I want to have the New Leads records that are on the New Leads Layout copy to the Closed Leads Layout what would be the correct way of doing this without duplicating?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Tom Grell

You can just have a Leads table that will contain all the leads. Have a field for "Closed" that can just be a simple yes or no field, with no being auto-entered when a new record is created. To show the new leads, do a find for "no" in the "Closed" field. To show closed leads, do a find for "yes".
Will Loving

If the fields are in the same record and the Open and Closed layouts are based on the same table, then you are not really copying between layouts, you're just copying between fields in the same record, some of which happen to be on one layout and some on the other.

As tgrell and I have both said though, it's more efficient and "normalized" from a database design perspective to simply have ONE set of these fields and a flag or "Status" field that indicates whether the Lead is Open or Closed. You then use Find/Omit or other forms of filtering to display just the records you want to see.

And, while tgrell suggests using a Yes/No Status field, with and auto-enter of "No", I personally prefer a number field called Status with no auto-enter. When the Lead is Closed, the Status field gets set to "1". For display purposes, you simply create a Value list with one value "1", add the Status field to a layout and set it to Checkbox with the "1" value list. Reduce the size of the field to show JUST the checkbox part and not the "1" to the right. Then put a label next to it that says "Closed".
noad

ASKER
Willmcn

I understand what you explained above....
So if I do that I have a layout called close leads I want to make it so that it show only in list view, ( I already have it showing in list view ) then if I only want to show the records that are closed on that layout what would be the best was the best was to filter it?

In other words when the user clicks on the closed lead tab it will take them to the closed lead layout and there they can only view that lay out in list view and that layout will only show then closed records . I understand that whatever filed I put there it will show, but if I  only want to show the close lead automatically when the user enters the layout what do I need to do to filter it?

Right now I placed the fields named closed and that's what it show, if I don't have those fields anymore because I will delete the closed leads layout the the only fields to place are the same field that are on the new leads layout "fields" Date, Dealr Name, Contact ect....
So without them having to do anything I want then to view the layout close lead and only show the closed records.

I'm trying to do the right thing, I see what you guy's keep telling me, not to duplicate data. I'll admit I don't fully understand why not, but while I'm still very new with FMP I like to think that I'm smart enough to know that when I hear the same thing over and over it's time to correct it.

The above will then lead me to ask the following question, the user want to make it so that the records that is input in the New leads layout is not longer visible once the record have be tagged closed. can that also be done?

Also from the closed leads layout they need to be able to go back into the record that they choose so that in the future they can edit the record.
Let's say someone calls and says that they moved. I want them to be able to go into that record and edit it.

If I'm begging to understand this correctly, there will only be one table, that table will hold the data and they I would create layouts as needed to display the data in different ways using filter???

Thank you for all of your help.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Will Loving

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Tom Grell

I just wanted to add that willmcn is correct in everything he has said. He obviously does this day in and day out for a living, and his solutions offered on this site are always excellent. I have used Filemaker for years (starting with version 3) as a small part of my job, and the wisdom willmcn offers comes with time and experience. It just takes a great deal of patience.
Will Loving

Thanks tgrell :-)  

I think I started with FileMaker II (no Pro) in 1987 or 88 on a Mac Plus with 1MB of RAM and a 20MB HD. Didn't start using it seriously until about '94 but have been almost full-time with it for a very long time.
noad

ASKER
Guy's

I can't thank you enough for all of your help.
Willmcn has helped me out on a lot of this solution, I'm starting to understand it much better now.
I think I'm going to take Willmcn advise and after this project is done I'm going to just sit back and test a lot of different ways to do things.

I'm sure I'll be asking for help on exactly how to filter things, but I have a solid foundation on how to go about it now.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes