Jump to content

Invoicing Field Query

This topic is 5705 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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! :D

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

Link to comment
Share on other sites

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:


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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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 :D

Top Man!

Edited by Guest
Link to comment
Share on other sites

This topic is 5705 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.