moving portal records between solutions

[TJMAC]

Hi Everyone, 

In short, I'm trying to get portal records from one FMP file to another. The devil is, however in the details. 

I'm trying to repurpose a couple of template files from v18. "Estimates " and" invoices". I set up and customized the invoices.fmp12 and everything went pretty great- now we've decided to take the process to the next level and are trying to do the same thing to estimates.

They seem a good fit for each other but because we wanted to have estimates independent from invoicing (invoice numbering and PO reasons basically) I thought it would be a good approach to have a button (& script) to 'commit' an estimate to an invoice once approved. In order for it to not make a mess of invoicing with each estimate we make. Also considering that some estimates would never be approved etc. This 'commit' would take the customer information and the estimate line items over into a new invoice and voila. we get paid. 

I've got relationships set up and customer information lives in a table in invoicing.fmp12 and products live in a table estimates.fmp12 - I'm not married to the situation but it seemed to be the best way to do it as we already had customers set up and previously invoiced, and the products table would need more information in estimates. I've tried triggering a script in invoicing from estimates, and can only get it to create a new invoice, not populate it. 

I've tried 'set field' additions to an already existing 'add invoice' script included in invoicing but no love there. I've gotten by over the years being basically a script kitty, so if anyone has even any general approaches it would be a huge help. I've explored putting it al into a single file but I figure that I would still need to script the movement from quote or pre-invoice to invoice as this is a business affairs requirement - that estimates should have their own UIDs and that invoicing should be serial and unique on their own.

If I were to do it in the same file wth a status field is there  a way to have estimateID have it's own numbering and not influence invoiceID? It feels like I would need to script either the estimate ID or the invoiceID? There's other needs for each step but I feel like those should be doable once I get this part. (maybe something with 'object modified'?)

thanks a bunch everyone- i'm sure this is so very broad so please ask questions for clarity!

[comment]

My own preference would be to:

1. Keep everything in the same file; this is not a must, but it makes things much easier;
2. Keep estimates and invoices in the same table;
3. Add an Invoices table purely for numbering purposes.

The way this would work is that turning an estimate into an invoice would create a new related record in the Invoices table, with the EstiimateID serving as the match between the two tables. Then the InvoiceID becomes available through the relationship.

 

[bcooney]

If there’s any chance of future integration with Quickbooks online, then I’d stick as close to their data model as possible. You may wish to check out LedgerLink. https://www.geistinteractive.com/products/filemaker-quickbooks-online-ledgerlink/
( full disclosure, I work for Geist, but I do not receive any additional compensation for this recommendation).

you can sign up for a sandbox account for qbo and check it out.