DwightMunson1
Qrew Assistant Captain
Joined 7 years ago
User Profile
User Widgets
Contributions
Re: Raffle Entry for Pipelines Qrew Meet Up May 14th, 2025
I have very beginner level knowledge of Postman so this is awesome! Protip for formatting and validating JSON. I use these two websites all the time, and have been using them all day today as I'm building a pipeline to quickly add invoice line items to an invoice. https://jsoncrack.com/editor JSON Online Validator and Formatter - JSON LintRe: Re:age validation
I use this formula to get the employee's current anniversary year. Floor(ToDays(Today()-[Sigma Employment Date])/365.25) ---------------------------------------------------------------- var date DOB = [Your Date of Birth Field]; var number Age = Floor(ToDays(Today()-$DOB/365.25); If( $Age >= 18, "older", "less than 18 years old" ) ------------------------------ Dwight Munson ------------------------------Re: How to use Pipeline to find the sum of a field?
Instead of using search records, I would the Make a Request step to search for your records and return a JSON object of them, then use another Make a Request step to sum the data. As a little bit of an example, where I loop through records and create a time card up until a timestop variable: My current Step B: {# FID 8 = Related Job FID 13 = PGE Work Request Record ID (KEY) FID 33 = PM Hours Remaining > 0 FID 35 = Total Hours Remaining > 0 FID 36 = Job - Related Customer FID 37 = PM Automation Eligible FID 38 = Alternate PM Automation Eligible FID 41 = Project Completion % #} { "from": "MyTableID", "select": [ 8, 13, 33, 35, 36, 41 ], "where": "{37.EX.'1'}AND{8.EX.'{{a.related_job | int}}'}", "sortBy": [ { "fieldId": 41, "order": "DESC" }, { "fieldId": 33, "order": "ASC" }, { "fieldId": 35, "order": "DESC" } ] } My current Step C: { {% set myDataSet = "MyTableID" %} {% set myTimeStop = namespace(val=a.hours) %} {% set myTimeLimit = namespace(val=a.time_limit_variable) %} {% set runningTotal = namespace(val=0.0) %} {% set relatedTimeSheet = namespace(val=a.related_time_sheet) %} {% set relatedWorkCode = namespace(val=a.related_work_code) %} {% set relatedClass = namespace(val=a.related_class) %} "to": "{{myDataSet}}", "data":[ {% for row in (b.json.data) if runningTotal.val < myTimeStop.val %} {% set remainingTime = myTimeStop.val - runningTotal.val %} {% set currentTimeLimit = remainingTime if remainingTime <= myTimeLimit.val else myTimeLimit.val %} {% set currentValue = row['33'].value if row['33'].value <= currentTimeLimit else currentTimeLimit %} {% set runningTotal.val = runningTotal.val + currentValue %} { "37":{ "value": "{{row['36'].value | int}}" }, "39":{ "value": "{{row['8'].value | int}}" }, "28":{ "value": "{{relatedWorkCode.val | int}}" }, "42":{ "value": "{{relatedClass.val | int}}" }, "655":{ "value": "{{row['13'].value | int}}" }, "227":{ "value": "{{currentValue}}" }, "34":{ "value": "{{relatedTimeSheet.val | int}}" }, "742":{ "value": "{{a.id | int}}" }, "32":{ "value": "0" }, "108":{ "value": "M-Level Time Automation" } } {% if loop.last == false and runningTotal.val <= myTimeStop.val %},{% endif %} {% endfor %} ] } ------------------------------ Dwight Munson ------------------------------Re: Creating a 'foreach' style loop in pipelines
This should give you a good start. To me as long as you understand a little Jinja and API this is the easiest route to go. You may have to adjust depending on what data you're creating the records with. Such as date formatting or casting to a certain data type. Step A: Trigger, with field for quantity of records to create Step B: Make a Request URL: https://api.quickbase.com/v1/records Method: POST Content type: application/json Body: { "to": "tableId", {# Table Name #} "mergeFieldId": 3, {# Your Key Field - only needed if you're going to update records, not create new #} "data": [ {% for x in range(a.counter_field) %} { "FID": { {# Foreign Key #} "value": "{{a.field_label | int}}" {# I always cast numeric foreign keys to an int #} }, "FID": { {# Field Label#} "value": {{a.field_label}} }, "FID": { {# Field Label#} "value": {{a.field_label}} }, "FID": { {# Field Label#} "value": "{{a.field_label}}" } } {% if loop.last == false %},{% endif %} {%- endfor %} ] } ------------------------------ Dwight Munson ------------------------------Pipelines: Search Records, Get List of Unique Values Question
Hello! I have a pipeline that has been made better over time but isn't the most efficient thing. We have an hourly invoice. We add timecard to the invoice. However, one company wants some summary data we can only get by creating a record in a summary table. So the current pipeline: A. Timecards are added to an hourly invoice, and we have a checkbox to trigger the pipeline. B. The pipeline prepares a bulk record upsert to relate the timecards to the summary table. C. The pipeline searches for all the related timecards. For each timecard (loop): D. Add an upsert row to related the timecards to the summary table. E. Look up a record in the summary table, to see if it already exists. Condition: If not exists then F. Create it Out of loop. G Commit upsert to relate the timcards to the summary table records. What I would like to do is when I search the timecards initially I would like to get a list of the unique values for the summary table. Then use those unique values to search the summary table records to see if they exist, and if not create them. The way it's set now, while it's looping sometime a summary record gets created, then when another loops it tries to create it again, but it was aleady created. We're talking about hundreds of timecards. Can anyone think of how to search the timecards and return a list of unique values? Or just a better process? I'm open to any ideas. ------------------------------ Dwight Munson ------------------------------Re: Issue with Record Picker > Based on Report
Are you 100% positive that you have that report associated with the record picker in the new form? I had a problem recently that it wouldn't save the report as the picker because of some filtering issue or something. I had to change the report group or change the filter or something. ------------------------------ Dwight Munson ------------------------------
