Question about looping through data from MySQL

[Nathan D]

Although I have a lot of overall DB experience, I'm fairly new to FM. Here's my situation: I've connected to a MySQL db and have written a FM script to get some records from the uc_orders table on that db, loop through them, and act on each record. So here's what I have going on so far (please tell me if there is a better way of doing this):

Go to Layout ["uc_orders" (uc_orders)]

Perform Find [Restore] #this finds records where uc_orders::order_status ==payment_received

Go to Record/Request/Page [First]

Loop

#...I do some stuff here (not relevant to the problem at hand)

Go to Record/Request/Page [Next; Exit after last]

End Loop

I have confirmed that there are about 70 records in the MySQL table that match the payment_received criterion. The problem is that no matter what, my code only seems to make one loop through and act on the first record it finds, then stops. After I get the data, I do a dialog box that shows me count( uc_orders::order_id ), and this always returns 1.

Any ideas why I'm only getting one result? Feel free to make any suggestions for better ways to do what I've described too, since I'm fairly new at this FM stuff.

- Nathan

[bcooney]

Your loop structure is correct, so the problem is probably in the find. From the script, you are trying to find in the order_status field for records that contain the string "payment_received." That doesn't seem correct. Perhaps you should be finding records where the field "payment_received" equals 1?

[Nathan D]

order_status contains one of several different string values, like "completed", "in_checkout", etc. I need the ones that are set to "payment_received", so that's why I set my find to that.

[comment]

I have confirmed that there are about 70 records in the MySQL table that match the payment_received criterion. The problem is that no matter what, my code only seems to make one loop through and act on the first record it finds, then stops.

I don't think the loop plays a role here, either. You can test this easily by pausing your script after Perform Find and seeing how many records are found.

[Nathan D]

OK, so I did a count( uc_orders::order_id ) right after I do the Go to Layout ["uc_orders" (uc_orders)] step. This returns 1. Then I do the same count right after I do the Perform Find step, and this returns 1 also. Doing the count between the Go to Layout and Perform Find returns a record with an order_status of "completed"; after the Perform Find step it returns a record with an order_status of "payment_received". Somewhat confused here.

[Nathan D]

OK, I think there must just be something that I'm doing wrong here due to my lack of understanding of FM. After I do the Perform Find, the correct records are there in the uc_orders layout (I can manually scroll through them in the layout that my script opened), but my script just doesn't seem to loop through more than the first one. I do have Go to Record/Request/Page [Next; Exit after last] in the loop, so I'm not sure what the problem is.

[Nathan D]

Attached is a PDF of the entire script for reference. maybe the problem is somewhere else.

Script.pdf

[Nathan D]

OK, I have more information on this. If I get rid of the following code, I can loop through all of my records successfully (see PDF I uploaded earlier for complete code):

Go to Layout [ “iAdjustments” (adjustments_data) ]

New Record/Request

Set Field [ adjustments_data::Adustment_Reason; "Web Order" ]

Set Field [ adjustments_data::Adustment_Reason_Dump; "Web Order" ]

Set Field [ adjustments_data::Item_ID; $model ]

Set Field [ adjustments_data::Adustment_Method; "Result" ]

Set Field [ adjustments_data::Adjustment_To_Item_ID; "" ]

Set Field [ adjustments_data::Quantity_OnHand; inventory_data::Quantities_OnHold ]

Set Field [ adjustments_data::Adjustment_Number; 0-$qty ]

Set Field [ adjustments_data::Quantity_NewOnHand; inventory_data::Quantities_OnHand-$qty ]

Set Field [ adjustments_data::Adjustment_Factor; "" ]

Set Field [ adjustments_data::Date_Adjustment_Posted; Get (CurrentDate) ]

Set Field [ a_location_store_id_ref::Quantities_OnHand; inventory_data::Quantities_OnHand-$qty ]

Commit Records/Requests[ Skip data entry validation; No dialog ]

This code runs once successfully, but after that my script stops. I've tried raising an error, but haven't been able to get anything. Hopefully this helps pinpoint the problem.

[comment]

I would suggest you start at the opposite end. Create a new script that goes:

Go to Layout [ uc_orders ] 

Perform Find [ Restore ] 

Go to Record [ First ] 

Loop 

Set Variable [ $debug; List ( $debug ; Get ( FoundCount ) ) ] 

#

Go to Record [ Next; Exit after last ] 

End Loop 

Show Custom Dialog [ $debug ] 

If this works as expected, start adding script steps until you find the culprit.

Note also that regardless of the current issue you should be checking for the existence of related records before calling the Go to Related Record[] step.

[bcooney]

I think your gtrr might be the culprit. Without a look at your Relationship Graph, is this context valid:

"Go to Related Record [ From table: “uc_order_products”; Using layout: “uc_order_products” (uc_order_products) ]

[ Show only related records ]"

That is, from uc_orders, can you go to uc_order_products? What's that relationship?

I have to chuckle how your initial post left out the gtrr part of the script. I usually write gtrr this way:

gtrr (relationship)

if (get (lasterror) = 0 //I really managed to go!

do stuff

endif

some people use:

if ( not isempty ( relationship::keyField) )

gtrr (relationship)

do stuff

endif

Furthermore, your last set field is not in context, is it? Also, you go to next record, but you're still in the Adjustments_data context, not uc_order_products (if your first gtrr is successful).

So, overall, check that you're really where you think you are in the script. Add pauses or use debugger.

[Nathan D]

Thanks for the suggestions, guys. I'll try them out and get back to you.

[Nathan D]

I can get the related records correctly and they're related by a field called order_id. After some more testing, I've whittled the error down to something in the following code:

Go to Layout [ “iAdjustments” (adjustments_data) ]

New Record/Request

Set Field [ adjustments_data::Adustment_Reason; "Web Order" ]

Set Field [ adjustments_data::Adustment_Reason_Dump; "Web Order" ]

Set Field [ adjustments_data::Item_ID; $model ]

Set Field [ adjustments_data::Adustment_Method; "Result" ]

Set Field [ adjustments_data::Adjustment_To_Item_ID; "" ]

Set Field [ adjustments_data::Quantity_OnHand; inventory_data::Quantities_OnHold ]

Set Field [ adjustments_data::Adjustment_Number; 0-$qty ]

Set Field [ adjustments_data::Quantity_NewOnHand; inventory_data::Quantities_OnHand-$qty ]

Set Field [ adjustments_data::Adjustment_Factor; "" ]

Set Field [ adjustments_data::Date_Adjustment_Posted; Get (CurrentDate) ]

Commit Records/Requests[ Skip data entry validation; No dialog ]

The code will run once correctly and insert a new record into the table, but it stops cold after that. Not sure why. Can't get it to report an error.

Interestingly, this line works fine:

Set Field [ a_location_store_id_ref::Quantities_OnHand; inventory_data::Quantities_OnHand-$qty ]

It will execute this code and move to the next record.

I've attached the latest version of my script.

Script.pdf

[bcooney]

After the adjustments_data sets, you're not going back to the uc_order_products table occurrence. I don't see how the a_location_store_id_ref field gets set, since you're not in that context (but I don't know the relationships). You need a Go To Layout "uc_order_products" after the Commit (which you don't need).