Records cloning/multiplying when edited

[bstonehill]

I have a php script that enters x number of identical records into the table items and creates a relationship between each of those items and the corresponding record in the purchaseorders table. I'm generating id's using an MD5 hash. The purpose is to allow us to create many records at one time against a purchase order in one operation rather than 20-30+, and with all universal fields already filled in. Once the items are received I then go back and edit each record to put in the serial number for each item.

Everything works perfectly, the right number of records is put in, relationships are created successfully, and I'm able to pull them up and edit them. However, when I edit a record and then save it it creates an additional copy of the item in sugar, but not in mysql. These additional items continue to show up even if the page is refreshed.

What would cause items to show up that are not in the mysql database?

I installed the 5.0.0f mysql bitrock installer and upgraded to 5.1RC.

[bstonehill]

Anyone? What does sugar look at when it retrieves records? Does it do anything other than write the data to mysql when a record is saved? I'm thinking since the mysql records are still correct the problem has to be in one of these two areas.

[clint]

Not sure what you mean by "it creates an additional copy of the item in sugar, but not in mysql."

Your Sugar database is the mysql database, right?

All records will be retrieved from the database. There is nothing stored on the file system.

[bstonehill]

That's why I don't understand this problem. For example, I have 14 records of a particular item. If I edit anything on anyone of them it creates a duplicate of it. My record count on sugar goes up by one and I have an identical item to the one I just modified. However when I query the mysql database directly I still only have the original 14 records I started with and my total record count is the same as what I started with.

This discrepancy persists even after logging out, closing the browswer, and then logging back in.

[clint]

We use relationship tables to join entities. That table is used to drive the count in the subpanels. It sounds like that your relationship table entries are not being set properly.

What version of Sugar are you using?

[bstonehill]

I'm using 5.1RC on XP Pro.

I don't see anything out of the ordinary in the tables. Here is the code I'm using to create the records and relationships.

if(isset($_POST['submit'])){ //Check for form submission
      
      $i = 0;
      $qty = $_POST['qty'];
      
      //Retrieve item information from pricebook
      $sql = "SELECT * FROM inv_pricebook WHERE name = '".$_POST['item']."'";
      $result = mysql_query($sql) or 
        die("Pricebook Query Failed");
      $row = mysql_fetch_array($result, MYSQL_ASSOC);
      
      //Lookup purchase order id
      $po_id_sql = "SELECT id FROM inv_purchaseorders WHERE name = '".$_POST['po_number']."'";
      $po_id_result = mysql_query($po_id_sql) or
        die("There was a problem retrieving the purchase order id");
      $po_id_row = mysql_fetch_row($po_id_result);
      
      //Insert items into database
      while($i < $qty){
        $hash = md5(microtime().implode('', $row).$i);
        $hash2 = md5(microtime().$hash);
        $insert_sql = "INSERT INTO inv_items (name, description, part_number, retail_price, item_cost, warranty, id)
                       VALUES ('".$row['name']."', '".$row['description']."', '".$row['part_number']."', '".$row['retail_price']."',
                          '".$row['item_cost']."', '".$row['warranty_period']."', '".$hash."')"; 
        mysql_query($insert_sql) or
          die("There was a problem adding items to the database"); 

        //Create relationship
        //$id = mysql_insert_id();       
        $rel_sql = "INSERT INTO inv_purchasrs_inv_items_c (id, inv_purchashaseorders_ida, inv_purchassinv_items_idb)
                    VALUES ('".$hash2."', '".$po_id_row[0]."', '".$hash."')";
        mysql_query($rel_sql) or
          die(mysql_error());
        
        $i = $i + 1;
      }

[mikesolomon]

trap the sql statement that sugar is using to display the duplicate records then run it in mysql

i had something similar with cases where I had multiple contacts per case and in the case list it was displaying the same case multiple times

[bstonehill]

How do I trap the mysql? And if I find the problem in the mysql how do I resolve it.

[mikesolomon]

How do I trap the mysql? And if I find the problem in the mysql how do I resolve it.

edit include/ListView/ListViewData.php

under $main_query = $ret_array['select'] . $params['custom_select'] . $ret_array['from'] . $params['custom_from'] . $ret_array['where'] . $params['custom_where'] . $ret_array['order_by'] . $params['custom_order_by'];

put print $main_query;

This will show you the query that is being run

Then you can at least see what is going wrong

[bstonehill]

Okay, I've at least narrowed it down. The problem only shows up when search criteria is specified for the purchase order relation field which is going to be the primary search field. Any non-relation search fields are fine and I also have a relation field to accounts which is fine. The purchase orders relationship was created in module builder before it was deployed and the accounts relationship was created in studio after.

SELECT inv_items.id , inv_items.name , inv_items.part_number , inv_items.item_cost , inv_items.retail_price , 
inv_items.warranty , inv_items.serial_number , jt0.name inv_purchaseorders_inv_items_name, jtl0.inv_purchashaseorders_ida 
inv_purchashaseorders_ida, inv_items.assigned_user_id FROM inv_items LEFT JOIN inv_purchasrs_inv_items_c jtl0 ON 
inv_items.id=jtl0.inv_purchassinv_items_idb AND jtl0.deleted=0 LEFT JOIN inv_purchaseorders jt0 ON 
jt0.id=jtl0.inv_purchashaseorders_ida AND jt0.deleted=0 AND jt0.deleted=0 where ((jt0.name like 'Initial Inventory%')) AND 
inv_items.deleted=0 ORDER BY inv_items.name DESC

Here is the accounts one that does work:

SELECT inv_items.id , inv_items.name , inv_items.part_number , inv_items.item_cost , inv_items.retail_price , 
inv_items.warranty , inv_items.serial_number , jt0.name accounts_inv_items_name, 
jtl0.accounts_inmsaccounts_ida accounts_inmsaccounts_ida, inv_items.assigned_user_id 
FROM inv_items LEFT JOIN accounts_inv_items_c jtl0 ON inv_items.id=jtl0.accounts_insinv_items_idb 
AND jtl0.deleted=0 LEFT JOIN accounts jt0 ON jt0.id=jtl0.accounts_inmsaccounts_ida 
AND jt0.deleted=0 AND jt0.deleted=0 where ((jt0.name like 'COMPANY%')) AND inv_items.deleted=0 ORDER BY inv_items.name DESC

I can't find any discrepancies between the two.

I tried removing the relationships in module builder and re-deploying it so I could go back and add them through studio instead, but that didn't remove any of the relationships. Of course the relationships in studio are permanent so I can't redo them. Is there maybe a way to safely remove them manually so I can go back and add them in studio? Or is it more likely because the relationship is between two custom modules unlike the accounts relationship?

Thanks everyone for all the help!