need help with uh... hard to explain

[serzone]

Hi,

I've been working on a small database for my company, a database that creates invoices and keeps track of them

I have three files: one for Clients, one for Invoices and one for Services.

In the Clients database i have data regarding clients.

In the Services database is the data regarding the services offered and pricing.

In the Invoices database it all comes together and u can create invoices for a client with the services you provided.

Here's the problem.

In Services, for example Service1 has not one but 3 prices (being a copy center, the price differes on how many copies a client gets).

So

Service1 has Price1 Price2 Price3

Service2 has Price1 Price2 Price3

In the invoice i created a some repeating fields in which u can enter the services and i need the price to show up depending on the service provided.

ServiceName field is where u can choose the service

CopiesNumber field is where u can put how many copies a client is getting

ServicePrice field is where I need the price from Services database to show up.

The problem is that when in ServiceName in the Invoice database I select

Service1 the ServicePrice field shows Price1 or Price2 or Price3 (depending on how many copies there are) for Service1 as it was entered in the Service database.

BUT

when i go on to add to the invoice in those repeating fields

Service2 the ServicePrice field shows Price1 or Price2 or Price3 for the Service1 instead of Service2.

If i were to put on the invoice list first

Service2 i'd bring up prices for Service2 from the services database, but

when i add Service1 it still brings up prices for Service2.

How do i get it to take the prices for the record i am choosing?

[underhat]

Hi serzone,

First, I'm not clear on whether Price1, Price2, or Price3 are individual fields or a single repeating field. Either way, you may want to consider whether using a related pricing table is a better idea.

Repeating fields are a holdover from the earlier days, before FileMaker became relational. (You couldn't create relationships between files; it was a flat-file database only.) Repeating fields were a way to store multiple items of data even though you couldn't created related files.

Generally, it is not a good idea to use repeating fields for storing data. (They are useful for interfaces and scripting sometimes -- for example, if you want to have something that changes color based on a calculation, you can store the different colors in a global repeating field.) Repeating fields are harder to access, difficult to export, and can lead to problems down the road.

As for having multiple individual fields, you don't get much more flexibility with them, although they're less of a pain to export.

That said, sometimes repeating fields or several individual fields may be appropriate. Adding an extra table of prices may be more hassle than it's worth, particularly in FileMaker 6 (which can't easily access data through more than one level of relationship). Are you going to want more flexibility with prices in the future? In particular, might you want more than three prices? Or might different services have discounts at different numbers of copies (e.g. Service1 switches from Price1 to Price2 at 10 copies, but Service2 doesn't switch from Price1 to Price2 until 25 copies)? If you answer yes to these, then I'd seriously consider a separate pricing table. If your pricing requirements are fairly simple, then you might want to stick with what you have.

I don't entirely understand the existing setup you have; you mention that ServicePrice shows Price1, Price2, or Price3, but you don't say how it does this. What is ServicePrice defined as? Is it a lookup, or a calculation? How exactly does it access the prices in Services?

In any case, I would strongly recommend that you do not use repeating fields on the invoice. This is one situation where repeating fields are not the appropriate tool to use. First, as you've seen, it's difficult to work with them. They can exibit odd behaviour when using them in calculations and relationships. (I suspect you're running into a limitation where the first repeat of a repeating field is always used for certain things, such as relationships, but I can't say for sure since you haven't explained your setup in detail.) I believe that you will in fact not be able to set up anything simple that will pull in the correct prices for each repetition. You'd likely need to add a new calculation field and relationship for each repetition in order to get the prices!

Another reason for not using repeating fields is that you have to change your database if you want to add more repeats. What if you need to add more items to an invoice than you've allowed? Not only are you going to need to add more repeats to the repeating field, but you'll have to modify or add new calculation fields too for each new repetition.

Instead, I would suggest setting up an InvoiceLineItem table. The records in this table consist of lines on the invoice. In your case, this table would contain fields InvoiceNumber, ServiceName, CopiesNumber, and ServicePrice. InvoiceNumber connects the line item to an invoice record in Invoices. ServicePrice would be an (unstored) calculation, or a lookup.

You can then add and edit the lineitems on an invoice using a portal. For printing, you should start on the invoice record, go to related line items, and print it from a from the InvoiceLineItems file. (Portals don't print well, so it's better to print from the related file and pull in the invoice information through a relationship back to Invoices.)

Let me know if you want more info about how to set this up.

[serzone]

Thank you for your help underhat... but i still can't get it to work.

Actually if u have a second here's what the thing looks like. Lemme know if u get any ideas.

http://www.phpod.com/theend/temp/invoices.zip

Thanks, i really appreciate it.

[serzone]

bump

[underhat]

Oops, I'm sorry. I forgot that I have to add the thread to my favorite topics in order to get email notification when someone posts a reply.

I'm downloading the file and will look at it soon.

[underhat]

Hi serzone. A few comments:

Instead of using a number and a name for the relationships between Clients and Invoices, I'd suggest just using the number. We've had clients change their company's name before. If you're using the name in the relationship, it's a lot of work to change the name.

Moreover, the client number really ought to be an auto-enter serial number; it's always best to base relationships on serial numbers. If the client number is meaningful to the user, and might need to be changed sometimes, then create a second field under the hood that uses a serial number. A general good principle of relationship keys is that they should not be changeable and should not have meaning to the user. They should be a unique, and essentially meaningless identifier. The reason for keeping them meaningless is the same reason not to use the client's name -- any piece of data that has meaning to the user may need to be changed at some point. On the other hand, a serial number that has no purpose except to link records will not need to be changed.

It's not a necessity that you change this, but you may find that it avoids future trouble if you change it early on.

Now, as for the prices on the invoices, I tried several things (all involving use of the Extend() function, whose purpose is to make repeating fields play nicely with non-repeating fields, such as Services::Reg Price), and was unable to find anything that works. I also came across a message on the FileMaker Solutions Alliance TechTalk discussion group reporting the exact same problem you have; no one had an answer for it. In short, repeating fields and relationships often do not play nicely together; this has always been the case. With FileMaker 7, it sounds like repeating fields work okay with relationships as long as you're using lookups, but if it's a calculated repeating field, or a repeating field with an auto-enter calculation, you're out of luck.

I avoid repeating fields because, as you're seeing, they do weird things, can be hard to understand when it comes to using them in relationships and calculations, and aren't really relational (which means it can be hard to use the data in them for other purposes in the future, or hard to extract the data from them).

So, I still recommend doing this without using repeating fields. Instead of having fields with 6 repetitions, set up a related LineItems table (where each "LineItem" is a single row on the invoice). You can then display a portal on the invoice.

I've attached to this post (see the little Attachment link at the top of the post?) an example of the way I'd set up a LineItems table. See the new layout ("Invoices with LineItems") I added to the BRInvoices file. I chose to put the LineItems table in the BRInvoices file, since lineitems are really part of an invoice. I added a few fields to the Invoices table as well (they all contain "LineItems" in their names).

Note that you may need a separate layout in order to print invoices. Portals sometimes do funny things when printed. You may be able to get away with printing the layout with the portal, but if not, try the "Print Invoice" button I've added to my new layout. Note that the invoices are printed from a separate layout using the LineItems table, NOT the Invoices table. This is the standard way of printing related records. Another advantage of using a separate layout instead of printing the portal is that you can show an unlimited number of rows; it will grow to fit the number of related records. A third advantage is that the blank portal row (the one where you add new lines) doesn't print.

I also noticed that none of the total prices were calculating on your original layout (because the calculation uses the discount price, which always seems to be empty). I just copied your calculations directly from the repeating fields when I set up the LineItems table, so my layout has the same issue. This is something you'll need to address, since it's a question of setting up the correct logic in the calculations.

If you use my approach of a LineItems table, you would want to delete the repeating fields, and the Services and Sizes table occurences on the relationship graph, since they'll be obsolete.

invoicesnew.zip