Zapier looping skipping first row storing in SQL database

Hello ​@kitchen.jam 

You can probably cut this down to 5 steps and avoid the missing row issue. I believe it's how the loops are fed that causes the issues. I suggest using a code block to fix the shape of the data before looping.  There are a couple of approaches use a single SQL step that inserts only if not there and fetch what’s there and filter it with Python code. 

I think the first approach is best and here’s and idea of how to do it. (I have validated the code for errors, it’s just to show what’s possible)

Step 1. Code by Zapier (Python) “Build Rows”

Inputs you provide to the code step event_id, event_location, event_name, question and answer
 

# Returns rows like:
# [{event_id, event_location, event_name, question, answer}, ...]
from typing import Any
NA = "NA"

def to_text(v: Any) -> str:
    if v is None: return ""
    if isinstance(v, (list, tuple)): return ", ".join(str(x) for x in v if str(x).strip())
    return str(v)

def labelize(k: str) -> str:
    import re
    k = k.replace("_", " ").strip()
    k = re.sub(r"(?<=[a-z])(?=[A-Z])", " ", k)
    return k[:1].upper() + k[1:]

event_id = (input_data.get("event_id") or "").strip()
event_location = (input_data.get("event_location") or "").strip()
event_name = (input_data.get("event_name") or "").strip()
if not event_id:
    raise Exception("Missing event_id")

rows = []
exclude = {"event_id","event_location","event_name"}
exclude |= {k for k in input_data if k.startswith("zap_meta_")}

i = 0
for k, v in input_data.items():
    if k in exclude: 
        continue
    val = to_text(v).strip() or NA
    rows.append({
        "event_id": event_id,
        "event_location": event_location,
        "event_name": event_name,
        "question": labelize(k),
        "answer": val,
        "order": i,
    })
    i += 1

rows.sort(key=lambda r: (r["order"], r["question"]))
return {"rows": rows}

Step 2. Looping by Zapier “From List”

Pick the rows array from the Code step output.

Step 3. SQL Server Custom Query (inside the loop)

MERGE dbo.FormResponses AS target
USING (VALUES (?, ?, ?, ?, ?)) AS src(event_id, event_location, event_name, question, answer)
   ON target.event_id = src.event_id
  AND LOWER(LTRIM(RTRIM(target.question))) = LOWER(LTRIM(RTRIM(src.question)))
WHEN NOT MATCHED THEN
  INSERT (event_id, event_location, event_name, question, answer)
  VALUES (src.event_id, src.event_location, src.event_name, src.question, src.answer);

Map from the loop value
event_id
event_location
event_time
question
answer

Optional DB-side guard

CREATE UNIQUE INDEX UX_FormResponses_event_question
ON dbo.FormResponses (event_id, question);

I hope this makes sense. 

Best of luck,
Badger

Hello Badger, thank you for your response. 

Actually i don’t wanted to use code by zapier because in my previous topic i asked to optimize the tasks, basically i wanted to reduce the number of tasks.

So i was suggested to replace code by zapier with Formatter. 

this issue where first row is skipping sometimes it was happening before code by zapier replace with formatter and its still there.

so i was looking around that what is the issue and i found 

• Zapier sometimes starts the loop before the array is fully built or before index 0 is ready.

• Result: First loop (index 0) is skipped or half-populated.

and to fix this i have added the delay before the loop, to ensure that all data loaded before looping starts. But not working for me. 

Hi ​@kitchen.jam 

For us to have true context, post screenshots showing:

• how your Zap steps are outlined
• how your Zap steps are configured in the CONFIGURE tab while in EDIT mode with the field mappings visible so we can see the field types
• the DATA IN/OUT you are trying to use up until the issue with the looping

Okay let me explain in details (and let me know if you need more detail) 

1. Webhook to catch the submitted data from emailMe Forms

2. Formatter by zapier to get event id only 

3. Formatter by zapier to get event name only

4. Formatter by zapier to fix multiple images been uploaded

There are few more steps like this to fix images

5. Formatter by zapier to get all questions

6. Formatter by zapier to get all answers

7. Delay by zapier (added new step to add delay before loop started)

8. Loop by zapier to loop through all the fields (questions and answers)

9. SQL server -  Find Row via Custom Query to identify if record already submitted ( if not submitted it will go towards Add New Row and if submitted then it will go towards Update Row)

10. Path - to split into paths

11. Path - if record not exists

12. Formatter by zapier if field value is empty then put NA as answer

13. SQL Server New Row

14. Path - if record exists

15. Formatter by zapier if field value is empty then put NA as answer

16. SQL Server Update Row

​@kitchen.jam 

Can you post screenshots showing the DATA OUT from Zap step 1, so we can see how the data is originally structured?

TIP: Click field labels to see tooltips with more info.

NOTE: Max # of Loop Iterations is 500, but you have it set to 5,000

Hello Troy, thank you for your response, sure here is the screenshots from first step

Sending you more screenshots

​@kitchen.jam 

Zaps steps process in order.

Help links for using Looping: https://zapier.com/apps/looping/integrations#help

Loop iterations process in parallel unless programmed otherwise.

Guide for how to do sequential looping: https://community.zapier.com/show%2Dtell%2D5/guide%2Dhow%2Dto%2Ddo%2Dsequential%2Dlooping%2Diterations%2Din%2Dzap%2Dsteps%2D31166

For live Zap Runs in a Zap that use Looping, it will create 1 Zap Run per Loop Iteration.

e.g. Zap Run with 3 loop iterations will show 3 Zap Runs in the Zap Run history: https://zapier.com/app/history/

You can open a Zap Run history to see the details including the DATA IN/OUT for each step to help you trace the data flow and troubleshoot to confirm each Loop iteration is being processed.