HI there,

 

I'm trying to finish my invoice layout page. What I would like to be able to do is click a popover button to add an artwork (or artworks) to an invoice. I need to be able to list as many as necessary.

 

I have managed to get the invoice addressee working fine, which is a similar idea but not a list - just a single field, so a bit more straightforward.

 

I either need help with the relationships, or a script.. or somewhere it is going wrong.. I'll attach a series of screenshots rather than trying to explain more.

 

At the moment my button goes to my invoice data layout but and asks me to save changes, and generally doesn't work.. I understand for a list portal I need to create new data and this happens in invoice data. I'm working with a script provided on another forum and have been using the starter solution with filemaker but it's quite complicated to copy for my needs.

 

If anyone can wade through this it would be much much appreciated..

 

Thanks so much.

 

 

 

 

 

 

I'm assuming the artworks are unique pieces? And thus they will only be sold once, so they could not appear on two different invoices? If that is so, then you could simply add an invoiceID field to the artwork table and use that as a matchfield for your invoice artwork portal. Create a portal that displays all artwork that has no invoiceID (meaning they're not sold yet), and add a button to set the invoiceID for an artwork.

Hi olger. Thanks yea the artworks would always be unique. Even if they are editions they would have their own record entry.

Could you go into a bit more detail step by step? My artworks have a field called artwork preview, which pulls together the title, date etc. this is the field I want to be able to add as a list in a portal. I also want the image containers to portal so you have the artwork description and the image for each piece. Thanks!

Create a primary table called "Invoices" and "Artwork", with each having their own unique ID field (ie __InvoiceID and __ArtworkID, and a reference field _InvoiceID in "Artwork", I tend to use double underscores for primary key fields and single underscores for reference fields). You also need a constant field in each table always set to 1 or 0 (I tend to use 1 as I can also use that as a boolean).

 

At this point there's no point in creating additional Invoice TO's, but you will need an Artwork TO called "Artwork_List".

Now create a relation between the "Invoices" and "Artwork" table based on __InvoiceID and _InvoiceID. Also create a relation between "Invoices" and "Artwork_List" based on the constant field.

 

If you create a layout that shows the invoice, and also create a portal showing "Artwork", and a portal showing "Artwork_List", then all that is left is create a button on each line in the "Artwork_List" portal that will set the _InvoiceID field to the current __InvoiceID of the Invoice showing. On the "Artwork" portal you can create a button on each line that will clear the _InvoiceID which will effectively remove it from the invoice.

In each portal you can add whatever field you like from the table you are showing, or even a field from a related table.

If you have images in the portal, you'd need to increase the size of each row.

Also, it's the scripts I have trouble with. Do I not need scripts?

The only scripts you need is to set the Invoice ID in an artwork record, to add or remove to an invoice.

If you have a button on each portal row in the Artwork_List portal, then you can set the Artwork_List::_InvoiceID simply with a single line script:

Set Field [ Artwork_List::_InvoiceID; Invoices::__InvoiceID ]

That artwork should then show up in your Artwork portal (would be added to your invoice).

 

To remove an artwork from the Artwork portal, you can have a similar button on each portal row with a single line script:

Set Field [ Artwork::_InvoiceID; "" ]

 

That should do it.

One thing I haven't taken into account in the Artwork_List relation is making sure that only artwork is listed that has an empty _InvoiceID.

thanks, and when you say "additional Invoice TO's", is the "TO" - table occurance?

If anyone can wade through this it would be much much appreciated..

 

See attached file for an example.

 

PS: You may only be able to sell an artwork once (unless you're a really “good” salesperson …   ), but maybe you can offer it several times over to different prospective customers. In that case, and if you want to keep a history of these dealings, you may need a line items table after all.

 each having their own unique ID field (ie __InvoiceID and __ArtworkID, and a reference field _InvoiceID in "Artwork", I tend to use double underscores for primary key fields and single underscores for reference fields). 

 

The technical term is foreign key.

 

You also need a constant field in each table always set to 1 or 0 (I tend to use 1 as I can also use that as a boolean). […] Also create a relation between "Invoices" and "Artwork_List" based on the constant field.

 

You can set up a relationship using the Cartesian operator (x), which does exactly that (show all records from the other TO's table) without requiring any additional fields (a technique from the pre-FM7 era).

Artworks_eos.fmp12 2.zip

Thank you this is all great! I'll have a go with this template and see if I can transfer the ideas and methodology to my database. .The one thing I will need is a search option when you have the popover, otherwise a user would have to scroll through the entire database of artworks to find the one to invoice.