Invoicing Field Query

[Gawain]

Hi Guys,

My first post, so I'm sorry if it's in the wrong place.

I have a small database that is already operational showing Customers and Products, I've been trying for the last couple of days to get invoicing working, with no luck.

I'm trying to get the product name (Description) to show up from the Products Database when I input the product code (relations already created). Infact the solution I want is almost identical to the BPS invoice page. But it seems after the first line inputs correctly, any additional lines cause problems.

I've got a copy of "The Missing Manual" which clearly states that I should not use repeating fields for this function, but instead hook up two tables, I just cannot find how to do this?

Should I be using portals, lookup fields with repeating fields, or something else?

Please help - I've wasted 2 days on this and got no other work done!

Let me know if I need to supply any further information.... Thanks in Advance

[Fitch]

You have many customers. Each customer can have many invoices, but each invoice will only belong to one customer. So you have a one-to-many relationship between two tables, Customers and Invoices, related by customer id.

You have many invoices. Each invoice may have many products, and each product may appear on many invoices. This is a many-to-many relationship, which means (without going into database theory, you'll have to trust me on this) that it requires a third "join" table to go between Invoices and Products; let's call the join table Line Items. You have a one-to-many relationship from Invoices to Line Items, based on invoice id; and from Products to Line Items based on product id.

Your graph would look like this:

Contacts -- Invoices -- Line Items -- Products

You should look at one of the many example templates on FileMaker's site, in particular I'd steer you to:

http://www.filemakertrial.com/bpk/

This is a fairly complete solution yet is relatively easy to pick apart and understand (and it's free!) -- why reinvent the wheel?

[Gawain]

Thanks for your reply Fitch,

I was using the BPK for guidance while incorporating invoices into our existing solution, however as I wasn't looking at the bigger picture of relationship types (one-to many, etc) I think I may have overlooked this and just expected it to work.

I will give it a try and let you know if all works out.

Thanks again.

[Gawain]

Ok, So I've now setup the relevant join table as suggested and ensured the field types are set so as the relationships work correctly.

But I am still unsure as to how I should best implement the lines into the invoice. Am I to use repeating field lookups or a portal?

It seems when I use a portal I cannot enter data into the Product ID field, and when using repeating field lookup and entering the product code (or using drop down table to select a code) the product name field isn't changing.

I'm sure I've done something wrong but can't work out what.

I've included a screen grab of my relationship graph

Edited April 20, 2006 by Guest

[Gawain]

Okay, scratch that last request. I seem to have found a way to display them through a portal that requests the relevant data from the Line Items table based on the invoice number.

I hope this is the right way of doing it, if not - I'd be grateful for the advice.

:D

[Gawain]

Sorry guys, I seem to be turning this into my own personal topic. But I thought it best to post here as the above gives a bit of background.

I have, as explained above, got the portal working by reading in files from the line items table. The fields I have are Product ID, Product Name (description), Cost, QTY, Total.

All of the fields read in directly from the Line Items table except the total field which is a calculation of the cost * qty fields. The only problem is I now want a total field for all the lines in the invoice together. I cannot work out how to do this at all because of the way the portal works (only one total field which duplicates based on line entries).

Any ideas guys?

Thanks again in advance.

[-Queue-]

Have you tried a calculation in the Invoices table using Sum(lineitems::totalfield) ?

Alternately, a summary field in lineitems might be able to be placed on your Invoice layout (I know this unexpectedly worked in 7, but I am not sure if it has been 'fixed' in 8).

[Gawain]

Thanks for your reply Queue.

I hadn't tried either of those solutions, but have now and the Summary field from LineItems pasted into the Invoice layout has worked a treat

Top Man!

Edited April 21, 2006 by Guest