SQL: Querying Microsof company Excellent Flatlands – Summary for Data source Administrator/Developer
Looks like Microsoft company Excellent Flatlands becomes increasingly well-known, partially because of Microsoft company muscle tissue behind it. Now it is focused on the whole variety of straight and horizontally industry clients. Businesses use Little Business Administrator (which relies on the same technological innovation – Excellent Flatlands Skill vocabulary and runtime), Excellent Flatlands Conventional on MSDE is for minute midsize clients, and then Excellent Flatlands provides the other industry up to big organizations. There are several confirming resources available and you definitely need to know which one to use for different kinds of reviews.
If you are information source manager who is required to transfer some information to Excellent Flatlands or fix or duplicate information from one organization to another – look at this and you will have the signs on where to look further.
1. Microsoft company Excellent Flatlands Platforms Framework – Release Excellent Flatlands and go to Tools->Resource Description->Tables. Find the desk in the appropriate sequence. If you are looking for the clients – it should be RM00101 – client expert information file.
2. DEX_ROW_ID. This is the identification line and each Excellent Flatlands desk has it – this is due to the Excellent Flatlands Skill technological innovation. This line is never used as a key area – so don’t try to weblink your tables on DEX_ROW_ID. In situation if you need to exchange the desk from one organization to another you should use these queries:
Select * into GL00100_BAK from TWO.dbo.GL00100
alter desk GL00100_BAK fall line DEX_ROW_ID
insert into GL00100 choose * from GL00100_BAK
drop desk GL00100_BAK
The set of concerns above will exchange GL00100 (Account Master table) from TWO organization into your present organization. Then you need to run Checklinks – make reference to GP Guide – in purchase to reproduce the other consideration expert relevant tables.
3. Do not change the desk – sometimes it seems to be awesome if you just add several more content to the desk – like in IV00101 – stock expert information file have to just add several more explanations. If you do this – Excellent Flatlands Skill motor will don’t succeed in studying all your products – due of which DYNAMICS.DIC (main Excellent Flatlands Dictionary file) has actual information of all the tables and Skill uses it with and composing into the particular table
4. Take a moment to develop SQL opinions or saved procs. If you are assisting your inner designers to develop Amazingly Reports – good SQL opinions are actual help to them. Let me give you the example, the perspective below will demonstrate perform and traditional SOP Receipts – then Amazingly can just use it
Create perspective SOP_WORK_HIST
select SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP30200 where SOPTYPE=3 and VOIDSTTS=0
select SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP10100 where SOPTYPE=3 and VOIDSTTS=0
The above perspective will demonstrate everything and traditional non-voided invoices (SOP Kind = 3 remains for invoice)
5. Some fix/opening tips:
If you run question above against DYNAMICS information source – it will open up the customer, who unintentionally closed down your pc without signing off Excellent Plains:
delete ACTIVITY where USERID=’JOHN’
Next one will open up clinging batch:
update SY00500 set BCHSTTUS=0 where BACHNUMB = ‘JULYINVOICES04’