Help with Design

[-Queue-]

1) Actually, they are sorted. Month is a text field, so they are in alphabetical order. You will either need to sort by a calculated field of Month(Date), which will return a number, or create a value list of Months and change the sort for the Month field to be a 'Custom order based on value list' Months. Sort order isn't tied to a layout. It's based on the last manual or scripted sort performed. If you want to change the sort order in a script, select the Sort Records step and click 'Specify'.

2) You defined a leading subsummary by Month, yes, but the only field in it is Month. You'll have to add (Layouts -> Part Setup -> Create) a trailing subsummary by Month, if you want to put monthly totals below the details. Otherwise, you can add summary fields to the leading subsummary by Month.

3) You don't allow access to the layout menu. Instead you script navigation to other layouts using buttons. It is not possible to run a script upon selection of a layout from the layout menu.

4) Yes, they are. You've set them to be running totals, so the total for 2004 and the grand total will be the same. Deselect the 'Running Total' option in the fields' definition, if you want the totals to be grouped independently.

[faaslave]

Thanks -queue-,

I had been working on the layouts since I posted last, but even after reading your last post and trying to fix it, still some problems.

1) I am using trailing subsummaries, but still month totals don't calc correctly. Grand total works.

2) I would love to hide the layouts and have them only selected by the layouts, except I want access to them. I think I can get them off the menu above, but not the pull down menu on the side.

3) Month sort problem fixed, thanks

4) Do you know a good place to download icons and graphic elements to make good looking layouts?

5) What are my chances of winning the lotto?

Thanks alot,

Dave

[-Queue-]

1) On a subsummary part you have to either use a summary field or a field that is constant throughout each group of summarized records. Change your fields to be the same ones that you're using on the grand summary part. Also, move them at least one pixel down, so they are all within the subsummary part and will display correctly.

2) You can still access layouts that aren't in the layout menu by going into Layout Mode, navigating to a particular layout, and entering Browse Mode.

3) You're welcome.

4) You might want to post this question on the Right Brain forum. I mostly use my own graphics.

5) 1 in 15 million => buy lots of tickets

[faaslave]

Thanks

1) I am using the same summary field throughout the layout, the grand summary is a duplicate as are all of the others.: I will try the pixel thing

2) I don't want the user to be able to do it, just me. That can wait till I am done though. I am assuming there is some password feature, and I can set limits on what they can do

4) I'll try that

5) Those odds stink

Thanks Dave

[faaslave]

I stand corrected, I wasn't using the same fields.

IT WORKS NOW!!!

THANKS

[-Queue-]

2) You could put an Admin button on one of your main layouts that takes you to another inaccessible layout with a global text field formatted as a menu using a value list based on a field with a calculation of LayoutNames(Get(FileName)). Then a submit button could activate a script to Go to Layout [Layout Name by calculation; globallayout]. Since the calculation would have to be indexed in order to be used for the value list, when you add, delete, or change a layout's name, you would need to go into Define Fields, open the calculation, change it to 1 or something unrelated, click OK, click OK to exit the dialog, then re-open Define Fields, then the calc, change it back, click OK, and click OK. A bit of a pain, but the feature is quite nice otherwise.

[faaslave]

Whoa!!

That went over my head and landed somewhere in Nebraska.

The most important thing is to keep it so the user can't mess things up.

If I can't prevent access to the layouts, can I at least set it up so the user cannot change the template itself? That way they can only enter and delete data.

I looked through the account/privledges, and only saw read&write, or read/only, etc. I want her to be able to read and write, but not change the structure of the database.

Then I will feel O.K.

Cool Idea #1

Now back to getting fancy, but not like you had above, that is too much for me

Under the totals layout, can I create a button that runs a script that will ask the user for info?

example:

You click the button, and up comes a dialog

Enter Month, click here and it asks for a month and year, finds the fields, sorts, and displays just that data

Enter Year, etc

Enter Date range, prompts for a start date, then prompts for an end date, then does its thing

If it is going to be something like above then I will pass, but if filemaker can do this fairly simple, I will try it.

Thanks Dave

[-Queue-]

You can prevent access to the layouts by removing them from the layout menu (or leaving them on the menu but hiding and locking the Status Area so it isn't accessible.

To prevent modification of layouts, use the 'All view only' selection next to the Layouts option under the Data Access and Design section of a Privilege Set. Set the Scripts option to 'All executable only' to prevent script modification.

Since the Totals layout doesn't make sense in Browse mode, I would put a button on another screen or make a script available that prompts for which records to find, sorts, moves to the Totals layout, previews, and returns to the original screen. You could use Show Custom Dialog for this. However, it has issues if they enter data and then cancel. I would use a separate layout which only contains global fields for the month, year, etc. Then it validates the entries, performs the find, and continues to the report. See attached for an example.

[faaslave]

Very cool!!!!!!

Thanks alot -queue-

I broke my arm last night, very hard to do this now.

I think I have enough to start making nicer layouts.

Be back on soon......

Thanks Dave

[-Queue-]

Ugh! So sorry to hear that. By all means, give your arm a rest.

I'd better not see you on this forum anytime soon.

[faaslave]

Hello again,

I have a question, mostly for -queue- since he wrote this find feature. Which is great by the way.

I have been working on finishing up the new layouts, going with a cleaner look. In doing so I realized that when running the Invoice Summary script I didn't have an option for Show all records, I have a button on the layout for it now, and it would be great if there was a way to make it work with your script.

And another question, why on earth when I go into the Total2 layout, doesit refuse to show all the records in the preview mode, like your script will when it uses that layout? I show all records, then take a look, and no go. That doesn't make sense.

When I am done with this database, I will post it online for others to benefit from. I could not have done it without this forum. Especially -queue-, and Detlev.

Thanks a bunch

Dave

[-Queue-]

To add a show all possibility, prompt at the start of the script (after the Abort and Error Capture steps) whether the user wants to see all Invoices or a particular set. If they select 'particular', then continue with the loops and add an Else step just before the Sort step, followed by Go to Layout [Total2] and Show All Records steps. If they select 'all', then all the steps in between the If to Else will not be performed.

If you show all records manually, you'll need to resort the records manually, so that the subsummaries appear. Is that the problem?

[faaslave]

1) That is over my head. it sounds simple when you say it, but ahhhhhhh, would that be a show custom dialog? Then the else step I think I would mess up. Your script is pretty intense, but very impressive.

2) yes that fixed the other problem, wasn't sorted.

Soon I will attach an updated file. And I must say, it looks pretty good.

[-Queue-]

Yes, it would look like

Allow User Abort [Off]

Set Error Capture [On]

Show Custom Dialog ["Do you want to see all invoices or a particular group?"][Default Button: All; Button 2: Group; Button 3: Cancel]

If [Get(LastMessageChoice) = 3]

Halt Script

Else If [Get(LastMessageChoice = 2)]

Set Field [invoices::gMonth; ""]

.

.

.

Go to Layout ["Total 2" (Invoices)] <- from the original script

Else

Go to Layout ["Total 2" (Invoices)]

Show All Records

End If

Sort Records [Restore; No dialog] <- from the original script

.

.

.

[faaslave]

-queue-,

Here is what I came up with so far. See what you think

Thanks -queue-

[-Queue-]

Very nice. I would add a few things.

In the Totals script, add Allow User Abort [Off] to the beginning, so users can't click the Cancel button when the report previews; remove the Enter Browse Mode step and add Close Window [Current Window] and Adjust Window [Maximize] steps instead, so that the report is closed and the main window isn't half hidden. Add an Adjust Window [Maximize] step just before the Halt Script in the Cancel and Return script. And I wouldn't tile the windows in the Invoice Summary script, because the criteria window isn't large enough. I would either Adjust Window [Maximize] or Adjust Window [Resize to Fit] when the new window is opened.

[faaslave]

With the way I have it set up now, it solves my problem without too much effort. Now I need to start focusing on how to make reports that will print well, and include print buttons on the layouts. Then I also want to make sure that the user is prompted when exiting the program to make a backup. Maybe even automatically if possible. After that, I plan on setting it so the layouts cannot be changed unless I log in. That should do it, unless I think of something later. I will be working on that next, and beg for help if I get stuck. (Highly Likely)

A couple questions though (What a suprise?)

1) When I use scripts to open a new window, like for the help screen, the original screen changes shape. Why?

2) Is there a way to have my phone number fields force the data into a format like (661) 555-1212? I am sure there is, but I don't see this option

3) If I want the Membership status to fill in with certain text color or fill color, Do I make a field and put it behind the status field, so it looks like it is changing when it really isn't?

Thanks Dave

[faaslave]

Thanks -queue-

I will look at it when I get home tonight. I think we posted the last one about the same time.

Dave

[-Queue-]

1. You're using the Arrange All Windows step, which is going to resize all open windows so that they all fit on the screen simultaneously. This is what I meant when I said I wouldn't tile the windows.

2. Yes, but it's much easier to use an auto-enter calculation that overwrites the entered data with formatted data and has 'Do not replace existing value for field' deselected. This has been discussed during the last week or two. I'm sure you can find a thread that will give you an explicit formula to use.

3. If you want a different text color to be used depending on what the status is, this can also be accomplished using an auto-enter calculation and was discussed within the last day or so on another thread. A different fill color will require a calculated container field that uses global container fields (or repetitions from a global container) to use the correct color (or none) depending on the status. Case( Status = "this"; gRed; Status = "that"; gBlue ) This field would be stacked below the status field, and the status field would be set as transparent.

[faaslave]

1. That makes sense

2. I found it. "(" & Left(CONTACT_PHONE_NUMBER;3) & ") " & Middle(CONTACT_PHONE_NUMBER;4;3) & "-" & Middle(CONTACT_PHONE_NUMBER;7;4))

Just so I understand it, the Middle(CONTACT_PHONE_NUMBER;4;3) is saying start with the fourth character from the left and give me three characters? If that were true, 1234567891 would work right. But if the user types 123-456-7891, then it would return (123) -45-6-78

3. I will try this, but why a global field? Does a global field mean it can only have one value? Sounds right.

Thanks again,

Dave

[-Queue-]

2. Use the Filter function first, to clear out non-numeric characters. Let( P = Filter( CONTACT_PHONE_NUMBER; "1234567890" ); "(" & Left( P; 3 ) & ") " & Middle( P; 4; 3 ) & "-" & Middle( P; 7; 4 ) ). Then it doesn't matter what extraneous characters are entered, because they will be ignored.

3. Global field values are the same for every record in a table. So you can create a box in Layout Mode with the fill/border color you want, copy it, then paste into a global container field in Browse Mode, and it will be available to all records. Otherwise, you would have to copy and paste into a normal container field for every existing record, and do the same for each new record that is created. It also takes up much less space, because it only has to be 'stored' once.

[faaslave]

Thanks alot,

I have uploaded the current file, if you would please take a look. I got the phone number thing to work out.

I also tried the container bit, but it doesn't seem to work. Could you see where I have gone wrong?

Thanks Dave

[-Queue-]

I wouldn't make the container calculation an auto-enter. I don't think container calcs work that way anyway. Change it to a calculation field. Also, format the field's behavior so that it does not allow entry.

[faaslave]

Thanks -queue-,

That was the problem, I changed the field to a calc, no entry, and it works fine now.

Question, I found several places where they talk about validation, but most disscusions assume you know how to do it. I, of course, can't figure out this simple task.

How do you set validation to check for multiple items.

Lets say you wanted, field must have a @,(anywhere in the field), at least 4 numbers, and no j's, k's or l's. Can you do all this on one field?

I would think that this would be simple, and I am sure that it is, but how?

Thanks Dave

[-Queue-]

You want to validate based on a calculation. Using your example,

Position( field; "@"; 0; 1 ) and Length(GetAsNumber(Substitute(field; "0"; "1" ))) > 3 and not PatternCount( Upper(field); "J" ) and not PatternCount( Upper(field); "K" ) and not PatternCount( Upper(field); "L" )

would work as a validation.

[faaslave]

Thanks again,

Position( field; "@"; 0; 1 ) "You would think it would be as simple as 'must contain(@)' or something similar. In reading the help file, I can't make sense of what exactly his is saying. What does the 0;1 mean?

and Length(GetAsNumber(Substitute(field; "0"; "1" ))) > 3 On this one I see that it has to be greater than 3, but once again there is a 0;1 If you are saying that the filed has to be greater than 3, why the 0;1? Does that somehow designate numeric values?

and not PatternCount( Upper(field); "J" ) and not PatternCount( Upper(field); "K" ) and not PatternCount( Upper(field); "L" )

This part makes sense, except this would only rule out capital J,K,and L? I thought that was what Upper meant.

Thanks -queue-

Dave

[-Queue-]

You could also use PatternCount( field; "@" ) in place of the Position function. They don't mean exactly the same thing, but what you're looking for in a validation is only a 'yes or 'no'. A zero would be 'no' and any other value would be a 'yes'. What the Position function does here is test whether "@" is present in field, starting at the 0th character and looking for the first occurrence. If the test is true, then it will return the first position of the @ in field. For example, if @ were the third character in field, it would return 3. If the test is false, then it will return zero and the validation will fail.

GetAsNumber("0a0b0c0d1") will return 1. By converting the zeroes into ones, using the Substitute function, we ensure that the leading zeroes will not be ignored. GetAsNumber(Substitute("00001"; "0"; "1" )) returns 11111. Since we are testing for at least 4 digits in the field, this ensures we account for all possible leading zeroes and don't return a false value.

Upper(field) converts every letter character in field to a capital letter. This allows us to test only for "A" instead of "a" and "A", etc. and still have the correct result.

[faaslave]

I went back and changed the body to the fields I wanted, instead of using a portal, and it worked. I am not sure if I am just working around the proper solution, but it works.

That way I do not need a second table with a self-join.

,

Why on earth can't the help section explain things like that? Then I wouldn't have to ask so many questions. I have tried to find a book that teaches all these calculations, and I have two already, but none explain it well or show examples on how or why you would use them. I think they just assume you are a programmer, and it should make sense.

I created a new layout to show all invoices, just like the portal in the client/invoices layout, except I want all the invoices. So I created a self-join with the invoice table, and tried to make a portal. It only shows one record. So I changed the fields back to the original invoice table, no luck. You would think by now, I would get this:(

I have the latest table, if you have the time to check it out.

Thanks as always,

Dave

[-Queue-]

If you want to show all the invoices in a portal, you need a relationship using the cartesian join (X) operator. This matches every record in both TOs to each other. Your relationship uses the = operator, which will only match one record to itself, where ID = ID.

You don't need a portal in this case though. Just put the Invoices fields on the layout and change the layout to View as List (View -> View as List, in Browse Mode).

The online help does explain the functions fairly clearly and provides examples to demonstrate how they work. It just takes practice figuring out how to combine functions efficiently, to produce the desired effect. I've learned the most from John Mark Osborne's site and from downloading all the sample files Ugo, DJ, Ray, Vaughan, Bob Weaver, and other gurus post on this site. There are so many possibilities for usage with each function and combination of functions that no one book could describe them all or list ones that would be specific to your solution. That is why we have these wonderful forums for such things.

Don't feel bad for asking all of your questions. If you don't ask, you may never learn. After all, that's why we're here, to help answer them.

[faaslave]

I see.

That is what I figured out after awhile. Since there is not a common field for all invoices, there would be no way to show them all with a portal, unless I was to make a field and put the same value in it for all invoices, then base the search on that.

So I did what you said and made the fields, and then went to list mode.

I put all the header info in the header, and only one line with the fields in the body. That seemed to work. Now comes the another problem. I decide to put a total in a trailing subsummary. Well that sounds easy enough. I take the Total field and put it in the trailing subsummary, nothing happens. Not only does nothing happen, I don't even see the trailing subsummary, as if it is invisible when I show the list.

So then I figure, I guess I can't just use the total field, put it in the subsummary and let Filemaker do the rest. Maybe I must have a summary field in the subsummary. Well that didn't work. So no luck on that so far.

Second, I was wondering if Filemaker has two capabilities that I can't find. One feature that would be nice is in layout mode, I could select any field, button, graphic, etc., hit a certain key, and it would show me everything about the selected item. (i.e. I select a field, click whatever, and up comes what field it is, its attributes, any calcs or formatting assigned to the field on this layout) That would be nice. Another nice thing would be to be able to have a field on a layout, and make it invisible in browse mode. This would be handy, especially while you are still working on the DB.

I see that alot of people on here are posting that they are having trouble making fields transparant. I thought that was just a matter of clicking on the top left choice that looks like 2 hollow squares under the fill tools. Maybe they are talking about earlier versions that didn't this. Or I just don't understand what they are talking about.

Thanks again -queue-, or do you prefer JT?

Dave

[-Queue-]

Subsummaries are only reliable when previewing and printing. You would have to put a summary total field in the header or footer to see it in Browse Mode.

You can get the formatting information for a field by right-clicking (I assume it would be Option-left-clicking on a Mac) on one. The calculation information is only available in the field definition. Portal and button formatting is available by double-clicking (Cmd-clicking?) the object.

You may call me either. The newspaper called me LJ last week, so I guess anything goes.

Update: it appears that a trailing grand summary is available in list view. When did this become an option?

[faaslave]

I see that alot of people on here are posting that they are having trouble making fields transparant. I thought that was just a matter of clicking on the top left choice that looks like 2 hollow squares under the fill tools. Maybe they are talking about earlier versions that didn't this. Or I just don't understand what they are talking about.

Thanks, I will try that when I get home.

What about the above? Do you know about the transparancy issue?

Dave

[-Queue-]

I believe you are referring to making graphics transparent, which appears to be much more tricky than making fields transparent.

[faaslave]

I did mean fields. There is alot of talk about it on here. In FM7 you just click on the two hollow squares under the fill section, and the field is transparent. So I must not be getting what they are talking about.

But I would like to know if there is a way to keep fields invisible on a layout. That way, in layout mode they would be there to see what is going on, but when a user is in browse mode, etc., they do not see the fields. I know I can just remove them from the layout, but it would be nice to leave them on in certain cases.

The only thing I have found is to be able to make them not print.

Thanks Dave

[-Queue-]

Hmm. I haven't seen what you're describing. But it's easy to make a field 'invisible'. Set it to have no border or a border width of 'None', no background fill, and set the font to match the layout part's color. Format it to not allow entry in Browse or Find Mode, and you're done.