Help with Design

[faaslave]

I can see that I am just a rookie at this and most everyone on here are experts. I could use some help with a database I am working on. Here is what I am trying to do:

One table has a client list, names, add, phone, etc etc

second table has a product list, product, price

third table is for invoicing,

What I want to be able to do is to go into the invoice layout, pull up the clients name from a menu, and add if neccesary.

Then in the product field the same thing.

I do not want the user to have to know the ID number of the client or the ID of the product. I have these fields, but I don't see why I can't start typing in the field and let FM find it for me.

I start typing Da, and possible names start coming up until it has narrowed to one, or a menu.

I understand the need for ID fields but that is not an easy way for the user to fill in the fields.

My invoice table is using the name field from the client table, and I am using a portal for the product,price,extended price.

I will gladly send the file if anyone is interested.

Thank you very much in advance

[-Queue-]

Do a search on this site for clairvoyance or "type ahead". See if that gets you moving in the desired direction.

[faaslave]

I am very grateful that you are taking your time to help me out. I have included the table, that way I hope what I am trying to say will make more sense. I looked at the products for clairvoyance, but that's another $140 out of my pocket. Not worth that. I guess i will have to make the best with what FM has to offer. The key is knowing which way to go. I only have a few problems, but they are big ones for me.

As you can see in my table, The client/hair, client/invoice, and client/comment layouts have most of the info. When I go to the client/invoice layout, below the tab I wanted a way to put a portal to list all invoices from that client with a total. The total spent field will keep track of the running total spent by this client. That way the Disount status field can look at the total spent field and come up with what kind of discount the client gets. When they reach a certain dollar spent, their discount goes up. Then the discount rate field looks at the discount status field and puts in the right variable for invoices.

The Total Spent filed is what I can not figure out how to do. And I must admit in the invoice layout I seem to be having a little trouble, but I think I am close.

Thanks again for all of your help, and anyone else on here that takes time out of their day to help others. Hopefully one day I can return the favor.

[Deep Thought II]

hi~ i checked ur database, here are a few things to take note of...

1) on ur invoices form, u don't have "Customer ID" field to be entered anywhere (thus all empty), and since ur [Clients] table is related to [invoices] with this field, it shows nothing on ur "Clients/Invoices" form.

2) if u want the user to select client names from a list, why not just use client names to relate to [Clients] table? i changed ur database to this way, however.

3) if u insist on using "Customer ID" to relate to [Clients] table (i.e. instead of using names), use a value list that displays from 2 fields (i.e. ID and Names), so the user can select ID and see names at the same time. then add another field on ur Invoices layout to display Name when an ID is selected.

hope this helps... i attached the changed file according to 2).

HairprototypeCopy1.zip

[faaslave]

Thank you.

The Name field works great. I still have the problem with the Product field. When I put in a product, it makes a new one in the services table. It doesn't seem to find the current service like the name field is able to find the client data. Can the product field work the same way as the name field? If you make a new invoice, I grab the client, and that works, then I go to the product line and it can't seem to find any services. I will attach the table again ifyou need it. I did put in a couple things to make it eaiser to test.

Also, how come I have to upload sit files and you were able to upload a .fp7 file. It won't let me do that? Just curious.

[-Queue-]

Your TotalSpent field should be calculated from the Clients table. Sum(Invoices::Total) should add the Total field for all invoice records related to a client.

Your Services portal on the Invoices layout won't work the way you want it to. There should be another table (call it LineItems) where you select the services for each invoice and perform the price calculation. The Services table should not have the Quantity and Extended Price fields. See attached for modifications.

BTW, Deep Thought attached a zip file, not an fp7 one.

HairprototypeCopy.2.1.zip

[faaslave]

Here is the latest issue. The Discount Status field calculation is inncorrect. If you fix it based on the Total Spent field, which is what needs to be done, the database file become corrupted. The Sum(Invoices::Total) works nice in the Total spent field. Can it display $0.00 if it doesn't have any data? This might be the problem. When Discount Status takes a look at Total Spent and doesn't see anything, that might be causing the database to crash. enclosed is the latest file

[-Queue-]

Clients::Discount Level is based on Clients::Total Spent, which is based on Invoices::Total, which is based on Invoices::Discount, which is based on Invoices::Discount Rate, which is based on Clients::Discount Level. See the problem? It's a closed loop. FileMaker can't determine the result because it's an infinitely recurring calculation. You need to find some other way of calculating the level. If you scripted Total Spent to be set when each invoice was processed, then it would be a static field, not dependent on the Invoices table, and a result could be calculated.

[faaslave]

Here is my latest version. I did as you said and made the discount rate a script. That did break the chain with loop. I made some other changes too. I thought I was really on to something with this scripting, but I am stuck again. What kind of beer did you say you liked? Help

Please take a look and laugh if you must, but if it means another hint, laugh away.

[dbruggmann]

No laugh, only some remarks:

1.) In your scripts you use

[dbruggmann]

Hi faaslave

Attached a copy of your file, to which I applied the things I wrote about in the previous post. What I did is really just one of several ways to choose from, it depends only on your needs. Keep asking!

One last remark: You have linked Clients to Invoices via the Clients Name as key field. This works, as long as there are not two clients with the same name. In my copy I changed the relationship to use the ClientID as keyfield.

Hair9-21.1Copy.fp7.zip

[faaslave]

Thank you very much, I will take a look at it, and try to learn.

After that I will be back on here, to ask how on earth you did it.

Thanks

[faaslave]

Thanks alot for the time you spent trying to help me. I see what you are saying about not wanting the old invoices to change as time goes by. Also needing the discount rate to be figured out after the subtotal has been determined. You made some very nice changes to the database. The only problem is that I am having a hard time understanding all of it. I am just not very good at this. I think maybe I should just stick to having the user manually put in the discount rate to keep it simple. The people that will be using this have no understanding how any of this works, and I need to keep the user interface very simple. As far as the names being related to the invoice instead of the cliet ID, I did it that way so the user could just pick a name, instead of having to know the clients ID. I figure I will just make the name field a unique field, and if a customer comes in with the same name, I will force the user to use a middle name or even have a "John Smith2".

I have a lot more questions coming up in the future that I would love some ideas on, and I hope you will be able to lend a hand. I just have to be able to understand everything, so I can keep it up.

For example, I want to know how to make a field use certain formatting based on the contents of a field. I know it can be done, but I am still trying to figure it out. Lets say a member is a bronze member, I could have the Membership status field have a background of bronze, and so on.

Another simple thing I haven't even been able to figure out, is how to copy formatting from one filed to another. Let's say that you created a layout using one a FM's templates, then you add a field later and it looks different. I should be able to copy the formatting, or save a color they used in the template, but I haven't figured out how.

I am having a lot of fun learning this though. And thanks for all the help from everyone.

[dbruggmann]

Hello faaslave

>> I think maybe I should just stick to having the user manually put in the discount rate to keep it simple. <<

That might be simple, but it's not very foolproof. Will they remember, which rate is for which client at what status? And chances are, that there will be more than one time, someone writes

[dbruggmann]

Sorry, but I had to split my reply into two posts. Here's the rest:

>> For example, I want to know how to make a field use certain formatting based on the contents of a field. I know it can be done, but I am still trying to figure it out. Lets say a member is a bronze member, I could have the Membership status field have a background of bronze, and so on. <<

Generally you need a new calculattion field and some global fields. The calculation would look something like:

Case(

MembershipStatus = "Bronze"; g_bronze;

MembershipStatus = "Silver"; g_silver;

MembershipStatus = "Gold"; g_gold;

g_white)

Into the g_fields you insert a rectangle with the respective colour, which is displayed by the calc field depending on the content ot the original field. You only have to make the calc field an exact copy of the original, place it behind and make the original transparent.

>> Another simple thing I haven't even been able to figure out, is how to copy formatting from one filed to another. Let's say that you created a layout using one a FM's templates, then you add a field later and it looks different. I should be able to copy the formatting, or save a color they used in the template, but I haven't figured out how. <<

That's easy: Click on a field with the correct formatting, choose the Format Transfer Tool beneath the menu row (the one that looks like a yellow paint brush) and click on the new field.

[faaslave]

dbruggmann, once again you have outdone yourself. Thank you very much. Since those questions were too easy for you, here is another simple one. How come FM doesn't have a folder of buttons, and icons to use when making layouts? When you made some changes to my layouts, you used little icons like FM has in the toolbars. How did you do that?

Dave

Air Traffic Controller

Lame FileMaker Pro Designer

[dbruggmann]

Hi Dave!

Again, there's no wizardry: I just copied/pasted the

[faaslave]

Thanks Detlev,

I'll take a look at it when I get home. I thought I tried to copy the small ones in the toolbar once before but was unable. Maybe I was just getting tired. Working on this is more fun than a crossword puzzle. I think I am hooked.

This all started because a friend of mine that is a hairstylist, wanted a database for her work, and I said "No problem, I'll get filemaker Pro out and whip something up for you."

It appears that this is when I opened my mouth, and stuck my foot in it. Having no programmimg experience does not help. For the most part, I understand what I need to do, I just don't understand the syntax, and correct format for the scripts and calculations. That is going to take some time.

I am very lucky to have found a forum like this, where people like yourself are willing to help others learn how to use this program.

ciao

Dave

[faaslave]

Well I am getting closer. I did some of the things you said and it works nice.

Could you take a look at my calculations on taxable subtotal, everything seems to be working but that field.

Also, I changed the ID and Name like you said, and it works. But is there a way to hide the ID field under the name field, so the user is clicking on the ID field getting the name they want, but only the name shows up when they click on it. In other words, can you have an invisible field on the layout?

Bonus Question:

If you have a template already made, can you import that template as a new table into your existing file?

Since I am making an inventory table next, it would be nice to take FM's inventory template and import it into this DB, that way I can keep it all together.

I uploaded the latest

Thanks Dave

[dbruggmann]

Regarding the ID and Name field:

1) select the name field, choose

[faaslave]

Thanks Detlev,

Next time you come to Los Angeles, drinks are on me.

Dave

[-Queue-]

As discussed in your other thread, you need two calculations for the taxable/nontaxable amount AND two fields that sum those calculations. The 'flag' calculations need to be in the LineItems table, so that there is one for each line item record. The sum fields need to be in the Invoices table so that you can easily sum the line items based on InvoiceID.

It doesn't make sense to put your flag calculations in the Invoices table, because Services::Taxable only references the service for the invoice's first related line item, whereas Services::Taxable from the LineItems table is a one-to-one reference. So move your cLineItemTaxable fields to the LineItems table, and just use ExtendedPrice. Then make the sum fields in Invoices simple Sum(LineItems::field) calculations.

Also, when testing whether Services::Taxable equals "yes", you either need to do it explicitly (field = "yes") or use not IsEmpty(field). If( field ... ) or Case( field ... ) will only work when field contains a number.

[faaslave]

Thanks again -Queue-, I will try it out.

Dave

[faaslave]

Well it almost worked, I did as you said, file attached. But the subtotal fields are still not working, I am pretty sure it has to do with the cLineItemTaxable and NoTax fields. I am using the IsEmpty calc, but no luck.

Thanks Dave

[-Queue-]

You're using cLineItemTaxable, instead of Services::Taxable, in your two calculations.

[faaslave]

Thanks -queue-,

It works now.

One question though, as I am entering items, everything calculates just fine. If I go back and change an item, it does not recalculate until I tab into the fields that perform calculations. Can this be fixed by indexing?

Thanks Dave

I almost did it right, *******!

[faaslave]

Well now I am trying to tie up loose ends and get this thing finished. As I study what I have so far, I realize I have a small problem. The items that show up in the portal in the layout table need to be using the lookup feature, so I don't have old invoices changes prices, when items change their price. If I sold something for $40, and then next month it is $50, I do not want the old invoices updating to the current price, This would be an accounting nightmare.

So I do my reading and low and behold, that is what the lookup feature is for. Well I tried it and I realized it is not that simple. The products are in the services table, which is linked to the lineitems table, which in turn is linked to the invoice table.

I went into define database, picked the lineitems cat, selected the serviceID, then selected looked-up value. Problem solved? Nope. When I went back to try it out, the price was not showing up in the invoice.

I am assuming that since the lineitems table is joining the invoice and services table, I am missing something here.

2 posts up I have a table for download

Help

Dave

[-Queue-]

Lookups are only performed when data is first entered or when the key field for the lookup is changed. If the key field is InvoiceID, for example, then you will have to either Set Field [invoiceID; InvoiceID] to relookup the current record's data or perform a Relookup to relookup for all records in the found set.

You wouldn't want the ServiceID to be looked up, as it is manually selected. You would want to add a field to lookup the Price in the Services table and change your Exended Price calculation to be based on it. Then change the Services::Price field showing in the portal on the Invoices layout to be the LineItems::Price field. Go to your LineItems layout, show all records, add the ServiceID field to the layout and put your cursor in it. Then select Records -> Relookup field contents. When you return to your Invoices layout, you will see the Price field has been looked up.

[faaslave]

Thanks -queue-,

I am not quite sure what you mean.

Set Field [invoiceID; InvoiceID], this field is in the lineitems table. What is this saying? Set the field to InvoiceID, what is the second InvoiceID mean?

I make a new field in the LineItems table named price. I make this a lookup field to services price.

Then I display the serviceID that is already in the lineitems table.

change extended price to be quantity * Lineitems::price

I still don't understand what Set Field [invoiceID; InvoiceID] is?

Then after it is all set and done, when i change a line item in the invoice it will relookup automatically?

Also when I change prices in the future, old old invoices won't be updated?

Sorry, I am confused

[-Queue-]

It was an example. As I stated,

If the key field is InvoiceID, for example, then you will have to either Set Field [invoiceID; InvoiceID] to relookup the current record's data...

I wasn't being specific to your solution, only providing an instance of usage.

Set Field [anyfield; anyfield] means exactly as it reads. You're setting the field with its own contents. This behaves the same as a Relookup, except it only affects the current record instead of the found set. It is similar to creating a new record and entering the value for anyfield, which triggers the lookup initially. You are merely overwriting the field with its own contents to trigger the lookup again.

Since your lookup is based on ServiceID, you can change any fields in the LineItems table except for ServiceID, and the Price will not change. Once you change the ServiceID or select the same one a second time, then the lookup is triggered and the Price field for that particular line item will update to the current Services::Price.

[faaslave]

I can't say I understand, I tried to fix it, and some of it works now. When I update a line item, everything calculates right away. But when I change a price in the service table, all the old invoices update the prices.

Does not compute, does not compute.

I am not understanding this nightmare.

Thanks Dave

[-Queue-]

You didn't change the Services::Price field in the portal to be LineItems::LineItemPrice. Notice that the ExtendedPrice is not changing to display Quantity * Services::Price, and it is unstored. That tells you that it's working correctly; you just have the wrong Price field displayed.

Note that since ExtendedPrice is no longer using the related Price field, it does not need to be unstored. Go into the Storage Options for it and deselect 'Do not store calculation results'. This will speed up the display of the portal, since it won't be constantly recalculating the value.

[faaslave]

I think it is fixed, I had it close except I hadn't unchecked that box. I did as you said.

Now when I create a layout for totals, other things don't work right. Or maybe I am not working right.

I have a layout now for totals, using a portal. I couldn't figure the summary features out, so I am trying to get the results a different way.

This layout would be used to see what the total of the invoices are, Total Tips, and Sales Tax. She would need this for accounting purposes. It would be really nice to be able to have promt come up and say, Do you want to see totals for a everything, year, month, week, or day. Then she could eneter the find criteria, and get the report she wants.

I hope FM can do this. But while I am trying to figure it out, the first problem is the totals are being grouped by client, and it is only totaling the first line of the portal. I didn't put anything in that would cause it to search by client, very confusing.

Thanks again, Dave

Chiefs 0-3, This is going to be a long football season.

[-Queue-]

Your Totals layout is based on Clients (in Layout Mode, go to Layouts -> Layout Setup and see 'Show records from'), so naturally your Invoices portal is based on Customer ID. Your TotalSalesSummary, SalesTaxSummary, and TipSummary fields are calculations in the Invoices table based on only one record. A Sum calculation that doesn't use a related field only sums the data in the current record. So your totals are coming from the first related Invoices record based on Customer ID. Either you need to change your layout so that it uses records from the Invoices table, in which case you wouldn't need a portal, or you need to put your sum calculations in the table that you're going to use for the layout and use Sum(relatedtable::field), or you need to use summary fields (if you don't end up needing the portal at all) in the layout's table.

Once you have the layout set up correctly, it will be easy to create a script to select the records to view or create a filtered portal for which your accounting personnel can change the criteria on the fly.

[faaslave]

Thanks,

I have a layout now for Totals that works pretty good. See what you think.

1) You will notice that the months do not seem to be sorted after the year. I don't see why they are out of order. And how do you change sort parameters after you have already made layout? I don't see where to do it.

2) There are no subtotals for the months, even though I put that in when I set up the layout using the wizard.

3) Is there a way to run a script, or force layouts to look a certain way when users open them? Say I select a layout from the pulldown menu on the left side of the screen under Layout:, not from the muenu up top. Well when I do that I want the layout to be in a certain view, resize the window, perform a sort first, etc. I know a script can do this, but how do you force a script to run just by selecting the layout.

4)My grand total fields don't seem to be adding the 2 years together.

Thanks Dave