I have invoices that I need to send to more than one company, therefore, I need to duplicate my portal and give it a new invoice number for each company. Can you help?
Can you be more specific? What data changes? Just the invoice number and company details? - Is it a new invoice with same contents to different customers? - Is invoice number auto-generated?
Most portal style invoices have an invoice for the current number and a portal that lists specific items purchased in a related table. Usually the invoice and the portal records are related by a serial ID number that may or may not be the actual invoice number printed on the invoice.
If I understand your request correctly, you want make a copy of an invoice, including all the "line items" listed in the portal.
Use the following script after editing it to refer to your tables, fields and layouts:
Set Variable [$NewInvoice, GetNextSerialValue ( get(fileName) ; YourTable::YourInvoiceID)]
Go To Related Record [Show Only Related Records ; From Table "YourPortalTable"; Using Layout "Your Layout" (YourPortalTable)]
Set Field [PortalTable::InvoiceID, $NewInvoice]
Go To Record/Request/Page [First]
Exit Loop If [Get(foundcount) = 0]
Go To layout [original layout]
Thanks for getting back to me.....Yes, it is the same invoice that needs to go to different people. Therefore, I need to send this same invoice to different people with a new invoice number for each person that I send it to.
I tried what you sent me, but the hour glass just kept showing and I had to shut it off to get out of it. Of course, it could be the person behind the keyboard.
Any more suggestions.
It's hard to tell from this side of the computer screen :smileywink:
The hourglass indicates that most likely, the loop isn't being exited and you're getting lot's and lot's of records.
Do you have a copy of FMP adv? If so, use the debugger too to step through the script one step at a time and see where things are going wrong.
If not, then Modify the script as follows:
Run the script and watch for what happens at each step where the script pauses, are you on the right layout? What's happening to the found set? Press the numeric keypad's enter key to continue the script until it reaches the next pause and keep watching. After a few loops, you may see where things are going wrong and can fix it.
In either case, press the Esc key to interrupt the script and report back here on what you find.
Ooops, I tried the new way that you sent me, but I really did something wrong this time....It made 17,643 new blank portals....
Now I'm confused.
If the right conditions for exiting the loop aren't made, you could easily get very large numbers of records.
What I don't understand is how you could do that with the new script. It's the same as the original but with added steps that pause the script each time a new record is created. You'd have to press the return key thousands of times to get thousands of records out of it.
Hence my confusion.
You did add both new script steps to your script didn't you?
It would be much appreciated if you could give us an example of your database structure otherwise (and Phil is absolutely right) it is difficult from this side of the screen. The more information you supply, the better help you can get.
Nevertheless, I know the feeling when we are stuck with a problem and would like to help you to move on. Personally, and for identical purposes, I rather use scripts and found sets to automate certain operations; therefore, I have a different approach instead of duplicating a portal. As an example, and supposing that your database structure is similar to the one below, I would use a script with nested loops, but first you need a way to isolate (select) the data that you need to work on – companies and invoice contents.
If your db has a similar structure to this:
COMPANIES => INVOICES => INVOICE_CONTENTS
Invoice_nr -> Invoice_nr
Company_ID -> Compny_ID LineItem_ID <- from your items table (Item_ID)
Note: Use a check box field Value List = “yes” in your companies table and layout to select the companies you want to send a copy of your invoice, say, comp_box. This can be used in a found set.
Then you could use a script similar to:
-------- Script generate invoices -------------
#Create a variable to store the invoice number that you wish to copy
Set Variable [$myinvoice ; Value: INVOICES::invoice_nr ] (or pass it as a scrip parameter)
# perform a find in your companies
Go to Layout [ companies]
Enter Find Mode
Insert Calculated Result [Select ; COMPANIES::comp_box; “yes” ]
If [ Get(FoundCount) = 0)
#Create a variable to manipulate companies records
Set Variable [ $comp_rec ; Value: 1 ]
# create a loop to run each selected company
Go to Layout [invoices]
# New invoice number auto-generated, gather its number
Set Variable [$newinvoice; INVOICES::invoice_nr]
Go to Layout [companies]
Go to Record/Request/Page [No dialog ; $comp_rec]
# pass selected company to new invoice and deselect it
Set Field [INVOICES::company ; COMPANIES::company_id]
Set Field [ COMPANIES::comp_box; “”]
# increment record variable and exit if > number of records in found set
Set Variable [$comp_rec; Value: $comp_rec + 1]
Exit Loop If[$comp_rec > Get (FoundCount)]
# prepares found set of items in invoice to be copied.
Go to Layout [invoice_contents]
Insert Calculated Result [Select ; INVOICE_CONTENTS::invoice_nr; $myinvoice]
# Create a variable to manipulate invoice contents. Must be decremented to keep new records apart from the loop range of records.
Set Variable [$item_rec ; Value: Get(FoundCount) ]
Exit Loop If [ $item_rec = 0 ]
Go to Record/Request/Page [No dialog ; $item_rec]
# duplicate the record to keep its contents
# change the duplicated record contents invoice number to the new invoice number
Set Field [ INVOICE_CONTENTS::Invoice_nr; $newinvoice]
Set Variable [ $item_rec; $item_rec – 1]
Go to Layout [Original Layout]
Show All Records
# amend where and as necessary to reflect your data.
I hope this helps.
Check your private messages. (Upper right corner, looks like an envelope.)
Click on KIDO above the icon on the left of this message to open a new screen. Then under Contact click on "Send this user a private message" and include your info.
Retrieving data ...