Jump to content

Geoffrey Gerhard

Members
  • Content Count

    20
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Geoffrey Gerhard

Profile Information

  • Gender
    Not Telling

FileMaker Experience

  • Skill Level
    Expert
  • FM Application
    16 Advanced

Platform Environment

  • OS Platform
    X-Platform
  • OS Version
    Recent

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. It isn't an issue for the plug-in to talk to whatever QB Company file has already been opened by a user. If you're looking at a "headless" connection, where the QB Company file is running without an accessible UI and get's opened by FMP, you'll need some way of identifying each branch's path to that branch's QB Company file. Is there any overlap between Customers, Vendors, and/or Items between the two branches? If so, successfully integrating the common elements with separate QB Company files hinges on a number of variables and quickly grows in complexity as it moves from a "kinda fragile" to a "pretty robust" integration. Simple and fragile would have FMP identifying all List records ( Customers, Vendors, Items, Terms, etc. ) using FullName. The problem here is that someone at BranchA changing the FMP Customer Name value will break the ability of BranchB to identify that record in their QB Company file. Complex and robust would have you tracking the ListID values for each branch's copy of QB. The problem here is that you'll have to create separate scripts for each branch's integration with QB, or create some kind of "abstraction" to determine which Branch's QB ListID value to use when assembling a QB Request. A third option would be identifying transaction data separately in QB using an otherwise unused ( or Custom ) QB field. Your QB Transaction Requests ( Invoice, Payment, Bill, ReceivePayment ) could pass a value to the specified field, and that field could be used as a Filter criteria for any Reporting in QB. A fourth option would be to split the FMP files into separate versions for each branch, and use FMP 17's ability to quickly update/deploy the database schema/structure changes. That would mean you have extra work when you do development work. None of these options are "pain free" in the sense that there's extra work involved for someone to make things go smoothly. If it were me, I'd probably lean toward the fourth option. HTH! Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  2. The process can appear "frozen" when the TxnID is empty and the QB Company file has a lot of Invoices. No clue why it would freeze and crash if the elements and their values are valid ( and not empty. ) Something like this... Set Variable [ $$Result; Value:PCQB_RqNew( "InvoiceQuery" ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID"; INVOICES::_ID_QBTxnID)] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "IncludeLinkedTxns" ; "true" )] Set Variable [ $$Result; Value: PCQB_RqExecute ] ...should produce an almost instantaneous result. BTW: In the early phases of writing and testing any QueryRq, you can save yourself a lot of wait time from a badly formed Request by applying one or more IncludeRetElement filters. They dramatically reduce the amount of data returned, and in the event that your Request is returning every record ( as happens when you're querying a transaction table and TxnID is empty/unspecified ) the difference in speed can be staggering. For your purposes, I recommend this... Set Variable [ $$Result; Value:PCQB_RqNew( "InvoiceQuery" ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID"; INVOICES::_ID_QBTxnID)] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "IncludeLinkedTxns" ; "true" )] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "IncludeRetElement" ; "LinkedTxn" )] Set Variable [ $$Result; Value: PCQB_RqExecute ] ...to winnow the data QB gathers and returns to just the LinkedTxn data for the target Invoice. HTH! Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  3. In your scenario, is the manually entered QB payment applied to the Invoice? If so, you can do an InvoiceQueryRq for the TxnID and IncludeLinkedTxns = true. The Response will include all ReceivePayment records applied to the Invoice. The LinkedTxn node includes the Type, Date, RefNumber, and Amount values, which may be sufficient to identify the manually entered ReceivePayment. You could also parse out any/all TxnID values for linked ReceivePayment records, and--assuming you're storing the ReceivePayment TxnID in FMP, perform a find for each. NOTE: I don't recall offhand if the LInkedTxn node's Amount value is for the ReceivePayment TotalAmount, or the Amount Applied to the Invoice, but I think it's the latter. If the ReceivePayment is not applied when recorded manually in QB, a combination of ( ModifiedDateRangeFilter or TxnDateRangeFilter ) and EntityFilter::ListID might help if you're looking for a ReceivePayment for a particular Customer. And if you're just looking to find any manually entered ReceivePayment records within a date range, use the ModifiedDateRangeFilter. While it may include some older ReceivePayment records, it will definitely include all those added within the specified range. As suggested above, as long as you're storing the TxnID in your FMP Payments table you can run FMP finds to identify any TxnID that is not a Payment::TxnID field value, and query for/use the ReceivePayment TotalAmount value to find the match among FMP with an empty Payment::TxnID field. HTH! Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  4. I can't find a current reference to support my recollection that "Code: -1000" is a generic error that covers a wide spectrum of possible errors. I also can't recall seeing "Message: No Response Saved" regardless of the Code value. I notice that "VendorRef::ListID" is now "VendorRef::FullName" that gets its value from a field named "xx::List_ID" and wonder whether that's the mismatch it seems to be? I also wonder whether the script is running from the correct context, given that it appears to be referencing Invoice fields as values in a Vendor Bill. Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  5. The screenshot shows steps that reference FMP Invoices and--seemingly--a ListIDCustomer value. A QB Bill expects a ListID for a Vendor. If you have one FMP record for a company that's both a Customer AND a Vendor, they are separate entities in QB and require a distinct ListID value for each role. HTH! Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  6. It's likely that the Customer record exists in QB, but is Inactive. The default view of the Customer List in QB is Active Only, as is the CustomerQueryRq. Update your CustomerQuery script to include "ActiveStatus" with a value of "All" to be certain that the proposed CustomerName is unique. HTH! Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  7. Not quite clear on what you're looking for. You could do a CreditMemoQueryRq that limits the Response to TxnID nodes using the IncludeRetElement, and then apply the FMP PatternCount function to the result. The pattern count of "<TxnID" occurrences will tell you how many total CreditMemos are in QB. Is your goal to pull all CreditMemos, to pull all CreditMemos applied to one or more Invoices, or something else? Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  8. The error likely speaks for itself. Consider these steps to track down the problem: 1) Confirm that the related Invoices::TXNID_New_File record has a value is in the field. 2) Make sure there's only one value, and that it's an Invoice TxnID. 3) Confirm there's a corresponding Invoice in QB. 4) Try doing an InvoiceQueryRq using the RefNumber displayed in QB, and see whether the TxnID in the Response matches the stored value in the FMP record. You also mentioned Credit Memos. In my experience, they're usually applied within the framework of a ReceivePayment. Your script does not show any steps that apply a Credit Memo's values to an Invoice. As noted in an earlier reply, moving data from one file to another can be pretty complicated. Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  9. The AppliedToTxnAdd steps cited above look right, as long as there's no CreditMemo AppliedAmount or DiscountAmount applied to the targeted Invoice. And any ReceivePayment record that applies to more than one Invoice will need to Loop through those steps.... Good luck! Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  10. Migrating data from one QB Company File to another is not trivial, with lots of potential traps for those new to QB. Potential pitfalls include: Account Balances, Inventory Counts/Values, Bank Balances, Receivables, Payables, Credit Memos, Bill Credits... The order in which you populate the new file is relevant, and especially so if there's Inventory Items on open Invoices or Bills. Vendors and Items need to exist before you can recreate the Open Bills, Bills need to be created to adjust the Inventory Counts/Values prior to Invoicing, as do Customers ( and Customer:Jobs, if relevant ), and Invoices need to exist before you can add ReceivePayments. And ReceivePayments that apply to multiple Invoices may need to be adjusted to reflect Invoices that were paid in full ( and therefore not on your list of Open Invoices. ) As I said, it's not trivial. If you need help, I've been doing this for years and would be happy to consult. I have experience and a tool for this, and for combining multiple QB Company Files into a single new Company File. Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  11. I'm not entirely clear on what you're trying to accomplish, but my initial reading suggests that you'd use the ReceivePaymentAddRq using the new QB file's Invoice(s) TxnID within each AppliedToTxnAdd node. The ReceivePaymentQueryRs should contain all other values needed to complete each AppliedToTxnAdd node in the ReceivePaymentAddRq, as long as the ReceivePaymentQueryRs includes the IncludeLineItems element with a "true" value. HTH! Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  12. In case you revisit this issue, it's worth remembering that the LinkedTxn node is essentially a related record that may have multiple occurrences. You'd use PCQB_RsOpenFirstRelatedRecord ( "LinkedTxn" ) to get the values of TxnID, TxnType, etc. from the first repetition, and then PCQB_RsOpenNextRelatedRecord to get values for each subsequent rep. If a Customer makes multiple payments against the same Invoice, it would show multiple LinkedTxn nodes with a TxnType of "ReceivePayment". Each LinkedTxn node for ReceivePayment and CreditMemo contains what most identify as the most relevant details of that payment: Date, RefNumber, Amount. Note that you will need to go to the ReceivePayment source record if you need to differentiate between the components of the LinkedTxn node's Amount value, which is the sum of PaymentAmount and DiscountAmount. ( If you applied a CreditMemo in that same ReceivePayment, the CreditMemo will be in its own LinkedTxn node. ) Hope this helps! Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  13. It's pretty simple to push an Estimate from FMP into QB, although there's usually not much benefit to doing so. Any reporting on Estimates can often be done in FMP, and Estimates in QB are non-posting transactions that have no impact on the balance sheet or income statement. I'm unsure if there's any way to "convert" the Estimate into a SalesOrder via the API, and a glance at the OSR ( Onscreen Reference ) does not show any way to create such a link. That said, it's certainly possible to send the same ( or adjusted if the Customer accepts only part of the Estimate/Quote ) transaction from FMP to QB as a SalesOrder record that doesn't point back at the Estimate. Like an Estimate, a SalesOrder is non-posting. This means that the only reason to pass a SalesOrder from FMP to QB is if the Invoicing is only done within the QB UI. If you are doing the Invoicing in FMP, I'd recommend pushing only Invoices to QB and skipping the process of pushing Estimates and SalesOrders unless there's an accounting/business rule ( or reporting requirement ) that cannot be met from the context of the FMP file. HTH! Geoffrey Gerhard Creative Solution sIncorporated 14000 Creekside Drive Matthews, NC 28105 704) 184-6852
  14. I hope you'll let us know what you find--I'm really curious to see what's needed ( and/or must be omitted ) to make it work. I couldn't find any direct reference to what's needed, but my reading of page 388 of the 2013 QBSDK_ProGuide is what led me to think it may be nothing more than an ItemRef for the ItemSubTotal. Are you attempting this using the Desktop version of QB? If so, the documentation is here... https://developer-static.intuit.com/qbSDK-current/Common/newOSR/index.html ...but it doesn't appear to say anything useful for your particular use case. Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
  15. I've never passed an ItemSubTotal in an InvoiceLineAdd node, so I'm not sure what--if any--detail ( other than the ItemRef ) is necessary . Does your InvoiceAddRq creation process sort the FMP InvoiceLineItems by their type ( parts, material, labor ) and then insert an InvoiceLineAdd for the appropriate ItemSubTotal after it processes the last InvoiceLineItem of each relevant type? If an InvoiceAdd that followed the model in your email included eight InvoiceLineAdd nodes in the model's sequence, I think it will work. Is QB returning an error? If so, what does it say? Geoffrey Gerhard Creative Solutions Incorporated 14000 Creekside Drive Matthews, NC 28105 704) 814-6852
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.