MySQL

MySQL: Assign Opportunities to User based on Parent Account

Posted on

Query to Select List:   SELECT * FROM opportunities LEFT JOIN accounts_opportunities ON accounts_opportunities.opportunity_id = opportunities.id LEFT JOIN accounts ON accounts_opportunities.account_id = accounts.id WHERE accounts.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86';     UPDATE Query: UPDATE opportunities x LEFT JOIN accounts_opportunities y ON y.opportunity_id = x.id LEFT JOIN accounts z ON y.account_id = z.id SET x.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86' WHERE […]

MySQL

MySQL: Assigned Calls to User based on Parent Account or Lead

Posted on

Select List of Calls to Update: SELECT * FROM calls LEFT JOIN accounts ON calls.parent_id = accounts.id LEFT JOIN leads ON calls.parent_id = leads.id WHERE ( accounts.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86' OR leads.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86' ) ;   Update Query: UPDATE calls x LEFT JOIN accounts y ON x.parent_id = y.id LEFT JOIN leads z ON x.parent_id […]

MySQL

MySQL: UPDATE or DELETE with JOIN Operators (Contacts)

Posted on

Same as previous post but this time with Accounts instead of Leads. List the Contacts related to the Accounts this employee has assigned: SELECT contacts.* FROM accounts INNER JOIN accounts_contacts ON accounts.id = accounts_contacts.account_id INNER JOIN contacts ON accounts_contacts.contact_id = contacts.id WHERE accounts.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86' AND contacts.deleted = 1 ; *** Now that I have done […]

MySQL

MySQL: UPDATE or DELETE with JOIN Operators

Posted on

I'm trying to update some records on Table C (contacts.assigned_used), but I have a Join from Table A (leads) and passing through in intermediary Table C. The query to show the filtered Table C (which is what I want to update) is: SELECT contacts.* FROM leads INNER JOIN leads_contacts_c ON leads.id = leads_contactsleads_ida INNER JOIN […]

Jasper Reports

JAVA: Compare Boolean Vale

Posted on

I require this to limit the Print when Expression Field on Jasper Studio. I had some trouble getting the comparison formule, because I used the typical: $F{rfq_c} == "VALUE" This formula did not work because it only applies when comparing strings. After a lot of trial and error the correct formula would be: new Boolean($F{rfq_c}.booleanValue() […]

OpenBravo

PgSQL: List and Remove Triggers

Posted on

I want to delete directly on the PgSQL database on my OpenBravo application but it has a los of triggers that prevent certain actions, first I want to list them and after that will want to disable them. List: SELECT event_object_table,trigger_name,event_manipulation,action_statement,action_timing FROM information_schema.triggers WHERE event_object_table='m_inventoryline' ORDER BY event_object_table,event_manipulation Disable: DROP TRIGGER m_inventoryline_trg ON m_inventoryline;

OpenBravo

PgSQL: Cannot Delete Line on Table.

Posted on

This happens when I try to manually delete a record on the Database   ERROR: update or delete on table "m_inoutline" violates foreign key constraint "m_inoutline_canceled_inoutline" on table "m_inoutline" DETAIL: Key (m_inoutline_id)=(8310BD9D63A94DB5A2294EC8E48C6C9F) is still referenced from table "m_inoutline". ********** Error ********** ERROR: update or delete on table "m_inoutline" violates foreign key constraint "m_inoutline_canceled_inoutline" on table […]

OpenBravo

OpenBravo: Delete Goods Receipt

Posted on

I have not found an immediate way to do it. Manually Delete Records from: m_matchpo m_transaction m_inout   SELECT m_inout.documentno, m_inoutline.line, m_matchpo.updatedby FROM m_inout LEFT JOIN m_inoutline ON m_inout.m_inout_id = m_inoutline.m_inout_id LEFT JOIN m_matchpo ON m_inoutline.m_inout_id = m_matchpo.m_inoutline_id WHERE m_inout.m_inout_id = '936753D37E954F72AA8C111F950EB2EB'

OpenBravo

OpenBravo: Inland Freight is taken as a Stockable Item

Posted on

Inland Freight, International Freight and other Service Items are reflected as Stockable Items and appear on the Goods Receipt. I was able to "duct tape" a solution forcing this items not to print on the GS Template, but it still accounted as an Item that was shipped from our warehouse (It even has to specify […]

Uncategorized

OB: Accounting Check

Posted on

I'm working on the Financial Statement of My Company, we sell Industrial Equipment and spare parts to big companies on Latin America; right now I want to make a report that shows my sales based on the inventory I've got on my warehouse, the basic structure is the following: What did I sell? (Use Sales […]