1 2 Previous Next 19 Replies Latest reply on Oct 8, 2013 3:07 PM by willrollo

    extra lines appearing in portal line items

      Title

      extra lines appearing in portal line items

      Post

           I have created a script that is designed to credit an invoice. It does work (although a messy script) but for some reason there are two extra blank lines in the line items portal of the 'credit note'/ One above and one below the actual line item to be credited. There is only one line item to be credited on any single invoice.

           Here is the script

            

             
      •           Freeze Window
      •      
      •           Set Variable [ $ID; Value:INVOICE DETAILS::ID_Invoice_pk ]
      •      
      •           Enter Find Mode [ Specified Find Requests: Find Records; Criteria: INVOICE DETAILS::fk_Invoice: “>1” Omit Records; Criteria: INVOICE DETAILS::Visible Invoice: “1” ] [ Restore ]
      •      
      •           Set Field [ INVOICE DETAILS::ID_Invoice_pk; $ID ]
      •      
      •           Perform Find [ ]
      •      
      •           #Duplicate Invoice Record
      •      
      •           Set Variable [ $InvNumOrg; Value:INVOICE DETAILS::ID_Invoice_pk ]
      •      
      •           Set Variable [ $InvoiceID; Value:INVOICE DETAILS::fk_Invoice ]
      •      
      •           Set Variable [ $TaxPercentage; Value:INVOICE DETAILS::Tax_Percentage ]
      •      
      •           Set Variable [ $Reference; Value:INVOICE DETAILS::Reference ]
      •      
      •           Go to Related Record [ From table: “invoicedetails_INVOICEITEM”; Using layout: “INVOICE ITEMS” (INVOICEITEMS) ] [ Show only related records ]
      •      
      •           Go to Layout [ “Credit Note” (INVOICE DETAILS) ]
      •      
      •           Duplicate Record/Request
      •      
      •           Set Field [ CREDIT::fk_Invoice details ID; INVOICE DETAILS::ID_Invoice_pk ]
      •      
      •           Perform Script [ “Copy Shipping to Shipping” ]
      •      
      •           Set Variable [ $InvNumNew; Value:INVOICE DETAILS::ID_Invoice_pk ]
      •      
      •           Set Field [ INVOICE DETAILS::fk_Credit; CREDIT::Credit Number ]
      •      
      •           Set Field [ INVOICE DETAILS::fk_Credit date; CREDIT::Credit Date ]
      •      
      •           Set Field [ INVOICE DETAILS::Tax_Percentage; $TaxPercentage ]
      •      
      •           Set Field [ INVOICE DETAILS::Prev Invoice; $InvoiceID ]
      •      
      •           Set Field [ INVOICE DETAILS::Type; "Credit" ]
      •      
      •           Set Field [ Invoices::fk_Invoice details ID; INVOICE DETAILS::ID_Invoice_pk ]
      •      
      •           Set Field [ INVOICE DETAILS::fk_Invoice; Invoices::Invoice Number ]
      •      
      •           Set Field [ INVOICE DETAILS::fk_Invoice Date; Invoices::Invoice Date ]
      •      
      •           #Duplicate Invoice Line Items
      •      
      •           Go to Layout [ “INVOICE ITEMS” (INVOICEITEMS) ]
      •      
      •           Go to Record/Request/Page [ First ]
      •      
      •           Set Variable [ $Item; Value:INVOICEITEMS::Item ]
      •      
      •           Set Variable [ $Description; Value:INVOICEITEMS::Description ]
      •      
      •           Set Variable [ $Quantity; Value:INVOICEITEMS::Quantity ]
      •      
      •           Set Variable [ $PriceUnit; Value:INVOICEITEMS::Price ]
      •      
      •           Set Variable [ $Taxable; Value:INVOICEITEMS::Discountable ]
      •      
      •           New Record/Request
      •      
      •           Set Field [ INVOICEITEMS::ID_Invoice_fk; $ID ]
      •      
      •           Set Field [ INVOICEITEMS::Date; $Date ]
      •      
      •           Set Field [ INVOICEITEMS::Item; $Item ]
      •      
      •           Set Field [ INVOICEITEMS::Description; $Description ]
      •      
      •           Set Field [ INVOICEITEMS::Quantity; $Quantity ]
      •      
      •           Set Field [ INVOICEITEMS::Price; $PriceUnit *-1 ]
      •      
      •           Set Field [ INVOICEITEMS::Discountable; $Taxable ]
      •      
      •           Go to Layout [ “Credit Note” (INVOICE DETAILS) ]
      •      
      •           Show Custom Dialog [ Title: "Message"; Message: "Invoice Credited!"; Default Button: “OK”, Commit: “Yes” ]

        • 1. Re: extra lines appearing in portal line items
          philmodjunk

               What layout are you one when you perform this script? On what table occurrence is it based?

          • 2. Re: extra lines appearing in portal line items

                 I am on the layout Invoice based on invoice details table. 

            • 3. Re: extra lines appearing in portal line items
              philmodjunk

                   And what is the relationship between Invoice Details and invoicedetails_INVOICEITEM?

                   

                        Go to Related Record [ From table: “invoicedetails_INVOICEITEM”; Using layout: “INVOICE ITEMS” (INVOICEITEMS) ] [ Show only related records ]

                   Does not seem to belong in your script as the very next line is a go to layout step back to a layout based on Invoice Details.

                   And it would seem that the first part of your script could be simplified to just:

                     
              •           Set Variable [ $ID; Value:INVOICE DETAILS::ID_Invoice_pk ]
              •      
              •           Enter Find Mode [  ]
              •      
              •           Set Field [ INVOICE DETAILS::ID_Invoice_pk; $ID ]
              •      
              •           Perform Find [ ]


                   Since ID_Invoice_pk should uniquely identify a single record just specifying that value should produce a found set of just one record. Or is this supposed to be the FK field that links invoice details to Invoices and it has been misnamed?

                   

                        #Duplicate Invoice Record

                   But you are on a layout for Invoice DETAILS, thus this duplicates a line items record related to Invoice instead of an invoice record.

                   

                        Perform Script [ “Copy Shipping to Shipping” ]

                   And what does this script do? Does it leave you on the same record and layout that was current before it was executed?

                   You have set field steps that reference a table occurrence named "Credit" while on a layout based on Invoice Details. What is the relationship between Invoice Details and Credit?

                   Stopping with that question until I hear back...

                    

              • 4. Re: extra lines appearing in portal line items

                     Hi Phil

                     It is a rather jumbled script - I hate to alter much of it as it tends to muck it up! Anyway, answers to your question...

                     I agree with your first query. Here is the relationship according to my ERD

                     Invoice details =invoice details_INVOICE ITEMS (id invoice_pk = Id Invoice _fk, allow creation of records)

                     I see it probably isn't needed here..

                     The first part of the script - I thin it was originally to identify the correct record that is to be credited - but I suppose as I am initiating the script from the correct record then this may not be needed here either...

                     The script to copy shipping details is just so that the various fields on the invoice are automatically copied over from the previous invoice that I am crediting, 

                     All my orders, quotes, credits and invoices are all based on the invoice details layout as there can only be one of each at any one time  - but the relevent quote /invoice/ order numbers are auto created from separate tables called quote, invoice, order etc.

                      

                      

                     The idea is, I assume, is for the invoice details data to be copied to a new record, but is linked to the original one via the deposit link field. The new invoice details record is then labelled as a credit, and the portal on it that is based on invoice items, copies over what was on the previous invoice record (but makes the prices a negative). It does do all this but as i said earlier, two extra empty lines are created in this portal..

                     Thank you for your help
                • 5. Re: extra lines appearing in portal line items
                  philmodjunk
                       

                            The script to copy shipping details is just so that the various fields on the invoice are automatically copied over from the previous invoice that I am crediting,

                       That doesn't answer this question: "Does it leave you on the same record and layout that was current before it was executed?"

                       If it does not, this could profoundly affect how the rest of the script functions.

                       And you have not explained what relationship links Credit to Invoice Details--this is also critical to how your script functions or fails to function.

                       I am only now starting to vaguely recall previous discussions of your project--please don't assume that I remember your design in sufficient detail to be able to understand your script--after over 50,000 comments posted to this forum, the details I can recall get pretty fuzzy!

                  • 6. Re: extra lines appearing in portal line items

                         The copy shipping to shipping script should copy the shipping information (address) from the original customers table (linked to the new invoice details record)

                         So it should trigger once the new invoice details record has been created, as the billing information is also autofilled. The shipping info does change when it comes to invoicing which is why I have a separate script for it. So once it is triggered, the layout should be Credit(invoice details) and the script should then start the line items part.

                         The two tables credit and invoice details are linked using  CREDIT::fk_Invoice details ID; INVOICE DETAILS::ID_Invoice_pk. 

                    In this way, once the CREDIT::fk_Invoice details ID is filled with the INVOICE DETAILS::ID_Invoice_pk.  data, a new record is created. I then set field the Credit number from the credit table, back to the invoice details table, fk_credit field.

                          

                    Don't worry, I know just too well how many responses you have submitted to my posts - And I know that I am just the tip of a very large iceberg!

                         It is ust so hard to explain how my db works as it is quite complicated! Best to answer your questions probably unless you face a dropbox copy!

                    • 7. Re: extra lines appearing in portal line items
                      philmodjunk
                           

                                So it should trigger once the new invoice details record has been created, as the billing information is also autofilled. The shipping info does change when it comes to invoicing which is why I have a separate script for it. So once it is triggered, the layout should be Credit(invoice details) and the script should then start the line items part.

                           I haven't asked what it does except for some very key details that are still unanswered. And It doesn't "trigger"--your script explicitly performs it.

                           I still need to know if that script does or does not leave the focus on the same layout and record in that layout that was current at the time the Perform Script step is edited.

                           Is Invoice_ID_pk an auto-entered primary key such that when the record is duplicated, it generates a new value in the new record? This will be the case for auto-entered serial numbers. It may not be the case for other auto-entered values.

                           Let's assume that none of those possible issues are issue. I think I see the problem:

                             
                      •           New Record/Request
                      •      
                      •           Set Field [ INVOICEITEMS::ID_Invoice_fk; $ID ]


                           That set field step is linking the new line item to the original invoice details record instead of the newly created credit invoice if I read this script correctly.

                            

                      • 8. Re: extra lines appearing in portal line items

                             Ok - I shall try to explain. When an invoice is first created, this 'copy shipping to shipping' script is performed as part of the 'create invoice' script'. 

                             This 'create invoice' script is performed from a layout based on 'invoice details' and has the portal to invoice details_INVOICE ITEMS. 

                             So An invoice is created, but actually all that has happened is that the invoice details::invoice number field now has a unique number that was created when the  'create invoice' script was performed, as mentioned before. 

                             If the invoice is to be credited, then the idea is for that invoice details record be replicated albeit with different ID numbers etc. It just needs a credit number (created in same way as invoice number) and also invoice number - all other details, pretty well, remain the same as the original invoice. 

                             This should end up with the original invoice record (invoice details) and a new credit record (invoice details). ALthough they are both technically invoices except the credit has negative amounts and a credit number.

                        Invoice_ID_pk is an autocreate unique number field so this should be different on the credit invoice. 

                        I tried your suggestion but it made no difference. I attach a screen grab of what happens. It is the blank lines that i do not want...thank you

                        Hope I answered your questions satisfactorily!

                        • 9. Re: extra lines appearing in portal line items
                          philmodjunk

                               What suggestion did you try? I made no suggestion--just pointed out a script step that looked incorrect.

                               

                                    Ok - I shall try to explain.

                               Sigh.... All I asked was for you to check to see if that script might be changing records or layouts on you in a way that affects the rest of your script. You could, for example, put a pause or show custom dialog right after the perform script step and then check to see where your are in terms of layout and record to see if you are where you should be at that point. If you have FileMaker Advanced, you can also use the script debugger to step through your script to see where and how this script is going wrong.

                               Before I can deduce anything from that screen shot in your latest post, I would need to know what is the underlying set of tables and relationships for that layout and portal. This layout is based on what table occurrence? and the portal is a portal to what Table occurrence?

                          • 10. Re: extra lines appearing in portal line items

                                  I tested this out:

                            "New Record/Request

                                   
                            •           Set Field [ INVOICEITEMS::ID_Invoice_fk; $ID ]


                            That set field step is linking the new line item to the original invoice details record instead of the newly created credit invoice if I read this script correctly."

                                 Just in case it was causing the error - so although you didnt suggest I change it, I thought worthwhile..anyway...

                                 I have removed the script step for copying shipping as think easier to test if it is being an issue...

                                 So I did that and there is no change to the end result, other than the shipping fields being empty

                                  

                                 Re your last questions, I thought I had explained it earlier but my FM jargon isnt quite up to scratch to explain!

                                 So , I have attached an ERD image. THis shows the main invoice details T.O linked to the Invoice Items table, which is what the is portal based.. I hope this helps!

                            • 11. Re: extra lines appearing in portal line items

                                   layout mode of the Invoice layout showing portal to invoice items. The scriot changes the layout to my credit layout but this is also based on invoice items...

                              • 12. Re: extra lines appearing in portal line items
                                philmodjunk
                                     

                                          I tested this out:

                                     

                                "New Record/Request
                                Set Field [ INVOICEITEMS::ID_Invoice_fk; $ID ]

                                     This has nothing to do with my comments on the Perform Script step. What I am trying to point out here is that it looks like $ID does not have the correct value to do what you want this script step to do, which is to link the new InvoiceItems record to the newly created Credit record in InvoiceData. $ID has the ID number of the original invoiceData record, not the new one.

                                     There is nothing in the script that you posted that can directly create three records in InvoiceItems. There is no loop and there is only one New Record step performed while on a layout based on that table. Thus, if you are getting multiple records in InvoiceItems, those records are coming from some other source.

                                • 13. Re: extra lines appearing in portal line items

                                       Thank you - I understand what you are saying - on closer inspection, it is actually one extra portal record being created. The one on the first line. I got confused with the last line (third) as it appears that objects appear on the next line of a portal despite a record not existing...

                                       So in fact it is as though there are two new record steps happening from somewhere...

                                       Will continue fiddling about - 

                                       I dont suppose you know a way of preventing objects appearing on  the next portal line do you? I thought it may be the 'autocreate records from this relationship' option in the relationship diagram but doesnt seem to make a difference...thanks

                                  • 14. Re: extra lines appearing in portal line items
                                    philmodjunk
                                         

                                               I got confused with the last line (third) as it appears that objects appear on the next line of a portal despite a record not existing...

                                         "Allow creation of records via this relationship" is the only option that can make a blank row appear after the last row in the portal.

                                         There are several possibilities that you can investigate:

                                         1) Make sure you have the correct record on your layout here. The script, as I mentally perform it, would create a new invoiceItem record for the original invoiceData record instead of the new credit record--that would fit seeing two items in this portal in some circumstances.

                                         2) Your script could be tripping a script trigger that performs some other script that is creating a record.

                                         3) The blank record already existed in InvoiceItems and when you created the new InvoiceData record it automatically appeared in the portal due to having a mathching value in your relationship's match field. THis might happen if you have been importing data.

                                    1 2 Previous Next