purchase order/invoice system

[e3digital]

I am trying to set up an invoice-type system, except I'm on the other end of it. I am a school media specialist and I want to use FileMaker to create my requisitions.

I used Hilton Brown's article "Invoices and Related Files" at http://www.jedi.apana.org.au/~hiltonb/ARTICLES/9804/9804TXT.HTM, and made a successful system (although I don't get why I would need the Products file when I have Line Items....)

Anyway, I need to be able to *purchase* items out of a catalog, rather than sell from my own inventory. Hilton's solution works great if you know the inventory, but I don't want to have to enter the items I want to purchase into Line Items.fp5 and then go to my requisition...know what I mean? I need to be able to enter the Item # and other product info from the catalog into my Requisition file and have it end up in Line Items...

Can someone please help me :-)

If it makes any difference, I work with 3 different media centers, each having their own budget. Eventually I want to be able to see my current budget as it changes with my purchases. (Kind of off the main subject, but it gives you a better big picture.)

Thanks,

Susan

[CobaltSky]

Hello Susan,

The products file is essentially your catalogue and stores a single instance of each item you may requisition. The line items file, on the other hand, stores each actual occurrence of a purchase of one or more products and ties that occurrence to a specific requisition.

In your requisitions file you should have a relationship to Line Items based on a RequisitionID field in both files. You should turn on the 'Allow creation of related records' option for the relationship to Line Items.

Then you should create a portal in your requisitions file which is based on the Line Items relationship, so that you will be able to enter items directly into a requisition (into the portal) and they will be stored in the Line Items file.

Your Line Items file should have a field for ProductID. You will be able to establish a value list of products taken directly from the productID field in the product/catalogue file. Attach the value list of products from the Products file to the ProductID field from the Line items file, where it appears in the Line Items portal in the Requisitions file.

Then when you select a product on a new line in the Line Items portal in Requisitions, a new record will be created in Line Items, and it will be linked to the product you've selected. Other product details (name, unit price etc) will be sourced from the Products file by the Line items file (automatically, via a relationship based on the ProductID field in both files). The quantity field (in Line Items) can be multiplied by the item unit price to calculate the line item extended price. This calc should take place within the Line Items file.

Then in the Requisitions file, aggregate calculations (unstored) with formulae along the lines of:

Sum(LineItems::ExtendedPrice)

- will give you the overall totals for the requisition.

That's it in a very compact nutshell.

[e3digital]

Ray,

Thanks for your reply... but I'm still not sure if I get it :-) And I may not have explained my situation very well....

I have 4 files, Requisitions, Vendors, Line Items, Products. (Using Hilton's model, I changed Clients to Vendors and Invoices to Requisitions)

Because I'm buying and not selling, I don't have an inventory or catalog. When I order something from a vendor, I'm looking at their web site or in the catalog they mailed me. With the paper and pencil method (which I'm trying to get away from), I simply write down the item #, etc. from their catalog onto the requisition form and manually do the math; and end up with a drawer full of paperwork. ;-)

What I'm wanting to do is do all of my ordering and budget paperwork with a FMP solution. I want to create a new record in my Requisitions.fp5 file, enter the product ordering information from the vendor's catalog, and see the result of my ordering on my budget.

This may be what you explained and I'm just not getting it...sorry if I'm being dense! :-) Could you give me another shot?

Thanks,

Susan

[CobaltSky]

Hi,

Yes, that is exactly what I explained - albeit in fairly 'condensed' language.

If there are terms or concepts in my previous post which you're not familiar with, perhaps you could say which they are (or just indicate which parts you are not understanding) and I'm sure I or someone else here will be happy to elaborate.

[e3digital]

So, when I want to order an item I haven't order before (so it doesn't show up in the Value List), will I just be able to enter it in my Req. file and it will appear in both Line Items and Products?

[CobaltSky]

The brief description I gave earlier was based on the assumption that all the items in the catalog would be entered into the products file in advance.

It's certainly possible to set it up so that you can add new items to the products file 'on-the-fly' from within the requisitions file, but that would require a couple of extra steps.

[e3digital]

CobaltSky said:

The brief description I gave earlier was based on the assumption that all the items in the catalog would be entered into the products file in advance.

That's what I thought, which prompted my second post. No big deal, because I'm not sure I even need that file.

Right now I have it working the way I envisioned it (thanks tons for your patient help), although I haven't added the budget component yet.

CobaltSky said:

It's certainly possible to set it up so that you can add new items to the products file 'on-the-fly' from within the requisitions file, but that would require a couple of extra steps.

If I decide to do this, what would the steps be? It seems to me I would have to have a relationship between Req. and Products and set it up to allow the creation of related records...is that right?

Thanks again for all your help and patience.

Susan

[Ugo DI LUCA]

My inventory could grow up to more than 1,000,000 products if all catalogs references where entirely added to the Product/Inventory file.

50,000 + records are currently registered, according to the collections we regularly sell, but there's no working day without new items, new collections, as well as new customers or suppliers are added to my Files.

You'd be daily adding items to "your" inventory, so that you can use their ID in current and ulterior purchases. That's all.

As Ray beautifully explained, there's very little difference from your System to the Classic PO/Invoice System, if this later really exist.

May be you should build your PO's by script directly from the Product File however. Still having 3 files for sure.

Being in the "source" file, you'd reduce the steps needed, and (but that's another part of the job), all entries to the line items, and cross-calculations, could be indexed. Suitable for your budget stuff.

Just an idea.

[e3digital]

Ugo DI LUCA said:

May be you should build your PO's by script directly from the Product File however. Still having 3 files for sure.

I'm not sure how I would do this...would it be better in any way that what I have working now? Are there advantages to it?

I agree that Ray explanation was great...it's so nice to have experts willing to help those of us wanting to be.

Thanks,

Susan

[CobaltSky]

e3digital said:If I decide to do this, what would the steps be? It seems to me I would have to have a relationship between Req. and Products and set it up to allow the creation of related records...is that right?

Essentially, yes.

There are a number of ways it can be set up. For instance, you can have an alternate layout with a portal to the products file - or you can have a script which puts up a dialog for the user to enter the product details into (and then writes the information to the products file as well as adding it to a new line item on the current requisition).

It's a matter of what works best for the users.

There are a few advantages in having a separate products file. If you keep up-to-date product details, preferred vendors, current pricing etc in a product file, it becomes a really useful reference and feed into the rest of the system. Then, for instrance, if prices change, your historical data in Line Items can remain unaltered, but you can enter the new prices into the Products file, and they will then be automatically picked up from there for all future requisitions.

Moreover, if it is set up so that Products operates seamlessly from within the Requisitions file, building and maintaining the data need not be too onerous.

[Ugo DI LUCA]

Susan,

Take a look at this sampler .

It simulate a "add to cart" solution, creating line items from the Product File.

Just an overview. The scripts, nor the Line Item aren't there, but you'd surely get the picture. If this may suit your need, we may help you define these little steps, which basically would :

- involve a switch to a "Entry Layout" to add product

- a sub-script in the Line Item

HTH