Forum Discussion

AlexCouts1's avatar
AlexCouts1
Qrew Trainee
8 months ago

IF we are mapping fields from an external application to tables in quickbase, is there a way to create a relationships between two records with a pipeline?

Hello! At our company currently we have the need to have vendors and employees submit receipts / invoices. These invoices will have amount, invoice ID, and maybe some vendor information. These invoices and receipts will then need to be correlated to specific Job IDs that will not be included when the invoice is uploaded. Currently, our accounting department seems to be doing this manually. 

My colleague and I reached a fundamental disagreement on how to improve data flow and are looking for some outside perspective to help forge a path forward. 

At this time, vendors will submit receipts through Bill.com. These "records" are then sent to our Quickbooks ledger. Once accounting does some processing the information is then sent to Quickbase where a record is created with a string of the job ID (mapped values). This creates an issue as users will then have to manually discover correlation between the invoice and the job from this table (and other places) in order to reconcile, because they are not actually related to the job in any way within the system.

My colleague insists that keeping the data flow as is (Bill.com > quickbooks > quickbase) is most ideal, and that through pipelines we can create record relationships between records. In this case, correlating the invoice record now in quickbase to the job ID record. I do not see any documentation supporting this kind of action and don't see it available as an option in pipelines immediately. Is this a possible action that we can take through pipelines? If it is not, are there suitable work-arounds? ideally, our user would be able to view an invoice record and click the ID as a link to hop the job, and see all invoices for a job from the job record without having to filter the invoice table each time. 

My idea for improvement was to swap the order of data flow (Bill.com > quickbase > quickbooks) allowing for there to be an association between the invoice and the job up front before being sent out the quickbooks. I realize immediate integration between bill.com and quickbase may not be possible without the use of JSON handler or Webhooks (both of which we are fully comfortable with) so optionally we would use code pages to capture the same information if needed. If we did need to pursue this avenue of data collection, what would you recommend? the vendors and employees that would need to submit would not have quickbase accounts with us, and the page should be relatively public facing. 

realize this is a lot of information and lot of secondary questions within one over arching scenario; any feedback or examples of past use cases would be much appreciated! Thank you in advance! 




------------------------------
Alex Couts
------------------------------

3 Replies

  • Hello. I would use the path with the least changes and effort, bill.comm-->quickbooks-->quickbase, you could send data from bill.com to identify the job, quickbooks has some note fields that can be used to "make this travel" fact.

    Thank you

    Marcelo Benavides Torres



    ------------------------------
    Marcelo Benavides
    ------------------------------
    • AlexCouts1's avatar
      AlexCouts1
      Qrew Trainee

      Marcelo and I have been discussing this issue away from the forum and I have already expressed to him that this is not the optimal outcome for our users since this will not create a tangible relationship in quickbase between the invoice and the job record and will only enter a text value into the field. I am looking for a way to create a tangible relationship between records through pipelines, if it is possible.



      ------------------------------
      Alex Couts
      ------------------------------
      • PrashantMaheshw's avatar
        PrashantMaheshw
        Qrew Captain

        If you can post some data from both tables without sensitive details , it might be possible to figure 

        For a tangible relationship to happen, QuickBase is irrelevant , it needs to be present before data comes into QuickBase via pipeline or otherwise , it could be artificial key or a combination of few records which makes business sense. 



        ------------------------------
        Prashant Maheshwari
        ------------------------------