SQL manually fill table like accounts_opportunities

[wivaku]

Hi all,

Environment: SugarPro 5.2.0c with MySQL 4.

Trying to populate a joint table, similar to accounts_opportunities.
Example: accounts and awards. With Studio I created the tables and relationships,
left to do: import existing data.

For my accounts_awards table, I have the fields: id, date, acccount_id, award_id.
The account_id and award_id combinations I can take care of, same for the date field.

The question: what is the recommended / required way to generate the id key?
Sorry in advance if this is a very basic question!

Thanks in advance,

-Winfred

[andopes]

The id key can be generated using the MySQL function UUID().

Cheers

[wivaku]

I was hoping it was something simple like that.
Thanks for taking the time to answer such a basic question!

[wivaku]

Ok André (and others), I feel bad for asking such a basic question before, so... let's make it a little bit more tough. :-)

What is the SQL/PHP code to achieve the following?

Pseudo code:
- for each <opportunity_id> from opportunities
- where team_id = <team_id_xyz>
- and my_custom_field = <partner_id>
- create record in accounts_specialopps
- set:
id = uuid()
date = now
account_id = <partner_id>
opportunity_id = <opportunity_id>

Thanks in advance!

[andopes]

The code is something like that:

INSERT INTO accounts_apecialopps (id, date, account_id, opportunity_id)
(
SELECT UUID(), NOW(), oc.my_custom_field, o.id
FROM opportunities AS o
INNER JOIN opportunities_cstm AS oc ON o.id = oc.id_c
WHERE o.team_id = '<team_id_xyz>' AND oc.my_custom_field = '<partner_id>'
)

Perhaps you need to modify something.

Cheers

[wivaku]

Super! Thanks André for the excellent and fast assistance. Me happy. :-)

[cmacholz]

One more question,

if you insert a new record and you want to TRACK that action in the tracker table...what value would go in the monitor_id field?


INSERT INTO tracker (monitor_id
,user_id
,module_name
,item_id
,item_summary
,date_modified
,action
,session_id )
VALUES ( '3c9ef974-5a28-698f-6ce5-4c114c1d075d'
,'d5056d8f-3844-6e26-301a-4acf9d3053ea'
,'Contacts'
,'5d7f76f3-f30e-bf7d-9b85-4c114c69e07c'
,'Test 2'
,'2010-06-10 20:33:41'
,'save'
,'d8rgbbv08v4deos0vgmh794s17')

THANKS!