Forum Discussion

DavidRice's avatar
DavidRice
Qrew Member
4 years ago

Help with pipeline/automation

I'm wondering if someone would be willing to help me with setting up a new pipeline. I am new to pipelines, and am trying to achieve the following:

In our Sales table, we are manually importing records from another database (actually from our website) from a CSV file. One of the text fields in this import is called "Product Text Import" and contains the name of the purchased product. We have another table in the same QB app called "Products" which contains a good majority of the products we have for sale, and so we need to link the imported sales to the related product. The problem is the import does not contain the record ID for the related product.

So, what we would like the pipeline/automation to do is whenever a new record is added (or imported) in Sales to search the Products table for a record there with the product name that matches what is in the "Product Text Import" field and then link it to that record in Sales by copying its record ID to the related product field.

And then, to take it a step further, if the automation does NOT find any product by that name, it should add a new record in the Products table with that product name and then link that new product to the new Sale.

Is something like this possible? I have been trying to build a pipeline, but am finding it a bit challenging-- probably because I am a beginner to pipelines. Any help someone can provide would be greatly appreciated. Let me know if any clarification is needed on the above proposed process.

------------------------------
David David
------------------------------
Pipelines

2 Replies

Sort By
  • AustinK's avatar
    AustinK
    Qrew Commander
    4 years ago
    Rather than trying to link things like this is it possible to just have the record id there in the data from now on? On the website make sure the record id is listed for the product in the first place so that the data can be properly related and you don't even have to do this. If you go this route and do text matching it is potentially inaccurate unless the product names are similar to skus. If they are, I think you could also just use the product name as your record id instead of record id itself but you would need to test that.
  • JeremyAnson's avatar
    JeremyAnson
    Qrew Cadet
    4 years ago
    If I've understood correctly, you want to do something like this:

    A: On New Event (which I use in preference to the Record Added/Updated steps in case I need to make changes later) - when record added to the Sales table, select the imported product name field for subsequent steps.

    B: Search Records - query Product with a limit of 1 where product name (or whatever your name field is called) equals the imported product name, which you can select from step A. This will look like {{a.imported_product_name}} in the pipeline.

    Insert a condition - check if the list of records from B is empty

    In the 'Then' branch:

    C: Create Record - create a record in the Product table with the product name equal to the imported product name, which you can again select from step A.

    D: Update Record - update record A, setting the product ID (or whatever your ID field is called) to the record ID you can select from C. This will look like {{c.id}} in the pipeline.

    In the 'Else' branch:

    E: Update Record - update record A, setting the product ID (or whatever your ID field is called) to the record ID you can select from B. This will look like {{b.id}} in the pipeline.

    I think this will work. I have had the odd issue with updating records from earlier in the pipeline and have added additional search steps, but I don't think that would be needed here. 

    Hope that helps.


    ------------------------------
    Jeremy Anson
    ------------------------------