when I choose a Product I will want to bring back more fields from the Product details - eg Size and Colour - how to I go about achieving that
The layout shown uses a portal to a line items table called "Invoice Data". As an Access user, think "sub form" and it may help you understand how it works. To add size and color fields, you add them to the Products table and then you have a key design choice as to what you do next:
1) Simply add the new fields from Products to the portal row after making space in the portal row for the new fields.
2) Or define fields in Invoice Data that use an auto-enter field option to copy the data from the new fields in Products and add them to the portal row instead.
If you use method 1, any changes to color and size will automatically appear on all invoices that list that product in the portal. If you use Method 2, only new invoices will show the change unless you take specific steps to update the data.
Hmmm, and I hadn't noticed before that this starter solution uses the name of the product (item) to look up an item from products. This often is not the best method to use for linking data in a relationship.
And I "speak Access" having done some fairly extensive work with that database application a few years ago so don't hesitate to describe what you want in terms of Access as I can probably translate that into "FileMaker".
Note that FileMaker does a lot of things very differently than how you would accomplish the same thing in Access. Don't be surprised to get a bit frustrated trying to set things up like you would in Access only to find that you have to use a very different approach. It takes a bit of time to familiarize yourself with the differences and learn enough about FileMaker to make the transition.
Thank you Phil - sounds like you understand my frustrations - especially as the terminology is different so its difficult to search for help on how to do what I want. I am taking it one step at a time, and have quickly made the decision that this database of mine doesn't have to be overly complicated! I just want to create customer invoices, mark them off when they are paid and know what my profit is on each order, and that's it!
I had already tried doing what you suggested and with the No 2 version as, of course, once an invoice has been submitted it needs to stay as it was at the time. I couldn't get it to work though so that's why I asked the question on here. I was doing something wrong though as I got the fields in the portal - set them to auto populate but they came back blank!
I am encouraged though - at least I was on the right track. I'll have another go this weekend - I found subforms on Access a lot easier!
@DavidAnders - Thank you for the list of links these will be very useful
Take a look at the field options for a field. If you define a text field in Invoice Data, you can use one of two approaches on the auto-enter tab to copy data from a field in products into your field in Invoice Data.
The looked up value option has been around for years and still works if you want to copy the entire contents of a field from Produicts into a field in Invoice Data. The calculation option can be used to do the same thing, or you can set up a calculation that both copies the data but also manipulates it in some fashion--such as combining the contents of two related fields into one result.
well after initial progress, I am completely flummoxed by this now.
I have changed the relationship between Products and Invoice Data to be Part Number (as this is unique) and on my Invoice I lookup the Part Number and this then auto populates item, colour, size, and unit price. My problem then comes from when I change the quantity, the Amount column then updates to "?". Please bear in mind that I haven't changed the Amount field calculation (that I am aware of) what do you think the issue could be?
The calculation is:
Let ([total = Qty * Unit Price ;discount = total * Discount Rate] ;total - discount)The strange thing (for me) is that when I preview the print invoice - the line amount totals are there and are correct, and yet as far as I can tell it uses the same fields and the form is using.Secondly - any ideas how you get the display to actually look like how you have laid it out it edit mode - all my labels are over the correct item, but then when I exit layout - they are not aligned properly!
If the number is wider than the field it will display a ?. You can make the field wider or the font smaller.
Thank you so much for that - I thought I was going mad!
Now I just need to sort my layout issues - all the fields are set to left align - with the exception of the unit price and line total which are right aligned. Any ideas?
Enter layout mode, select the portal by clicking the lower part of it (so that you don't click a field in the portal). Then, on the Inspector's position tab, clear the right "auto re-size anchor".
When you window expands to the right, your portal appears to be stretching to the right while your header text above it does not.
Thank you Phil - it all looks a lot better now!
Another question - I have added a field to the Invoice Data table - called Payment Ref. On my invoice in the footer there are the details of how to make payment. I would also like to include this payment ref field as this is unique to each invoice. However, when I put this field in the footer section, it shows the label of the field but does not display the data. It also does the same thing if I put it in the Trailing Grand Total section too BUT if i put it in the Leading Grand Summary section - it displays fine! Any ideas on what to do to fix it?
Invoice data is the name the developer gave to the "line items" table used to list records in your portal. If you want one such field for every invoice, you've defined this field in the wrong table. It should be defined in the Invoices table.