GL Tables | Victoria Yudin

I'm using the “SQL view for easier general ledger transaction searches” you ... However, it does not appear that great plains creates entries in the GL ... VictoriaYudin RamblingsandmusingsofaDynamicsGPMVP Home Resources GP Reports GPTables AboutMe Resources AllResources DynamicsGPBuilds DynamicsGPResources GPResources(builds18.x) GP2018Resources GP2016Resources GP2015Resources GP2013Resources GP2010Resources GP10.0Resources GP9.0&OlderVersions SQLServerResources MR&FRxResources GP Reports AllReports/Code CrystalReportsTips GeneralLedgerSQLCode InventorySQLViews PayablesSQLViews ReceivablesSQLCode SOPSQLViews SQLServerCodingTips System/SetupSQLCode GPTables BankRec Tables Company/System Tables FixedAssets Tables GL Tables Inventory Tables Payroll Tables PM Tables POP Tables RM Tables SOP Tables AboutMe FunStuff Posts GL Tables CommonlyUsedTables:GL00100–AccountMasterGL00102–AccountCategoryMasterGL00105–AccountIndexMaster[containsconcatenatedaccountnumber]GL00200–BudgetMasterHeaderGL00201–BudgetMasterDetailGL10000–TransactionWork/Unposted(header)GL10001–TransactionWork/Unposted(linedetail)GL10110–OpenYearSummaryMasterGL10111–HistoricalYearSummaryMasterGL20000–OpenYearPostedTransactionsGL30000–HistoricalYearTransactionsGL40200–SegmentDescriptionMasterDTA10100–MDATransactionGroupsDTA10200–MDATransactionCodesMC00200–MulticurrencyAccountMaster SummaryViews:GL01201–BudgetSummaryMasterView[containsconcatenatedaccountnumber]GL11110–OpenYearSummaryMasterGL11111–HistoricalYearSummaryMaster[Note:Theseviewsshowdebits,creditsandnetchangesperperiodperaccount,theydonotshowtheperiodbalances.ThereasontousetheseandnotthecorrespondingGL10110andGL10111tablesisthattheseviewsalsoincludetheAccountDescription.] ACCTTYPE(AccountType):1–PostingAccount2–UnitAccount3–PostingAllocationAccount4–UnitAllocationAccount PSTNGTYP(PostingType):0–BalanceSheet1–ProfitandLoss Active:0–Inactive1–Active TPCLBLNC(TypicalBalance):0–Debit1–Credit FXDORVAR(FixedorVariable):1–FixedAllocation2–VariableAllocation ACCATNUM(AccountCategory):1–Cash2–ShortTermInvestments3–AccountsReceivable4–NotesReceivable5–Inventory6–WorkInProcess7–PrepaidExpenses8–Long-TermInvestments9–Property,PlantandEquipment10–AccumulatedDepreciation11–IntangibleAssets12–OtherAssets13–AccountsPayable14–NotesPayable15–CurrentMaturitiesOfLong-TermDebt16–TaxesPayable17–InterestPayable18–DividendsPayable19–LeasesPayable(Current)20–SinkingFundPayable(Current)21–OtherCurrentLiabilities22–Long-TermDebt23–CommonStock24–PreferredStock25–AdditionalPaid-InCapital–Common26–AdditionalPaid-InCapital–Preferred27–RetainedEarnings28–TreasuryStock29–CommonDividends30–PreferredDividends31–Sales32–SalesReturnsAndDiscounts33–CostOfGoodsSold34–SellingExpense35–AdministrativeExpense36–SalariesExpense37–OtherEmployeeExpenses38–InterestExpense39–TaxExpense40–DepreciationExpense41–IncomeTaxExpense42–OtherExpenses43–OtherIncome44–ChargesNotUsingWorkingCapital45–RevenuesNotProducingWorkingCapital46–Gain/LossOnAssetDisposal47–AmortizationOfIntangibleAssets48–Non-FinancialAccounts[Note:Manyinstallationsthatwehaveseenhavechangedtheaccountcategorynamestobetterfittheirchartofaccounts.Anycategoriescreatedinadditiontothesedefaultsaremarkedwitha*inGP,eventhoughttheyhaveavalidcategorynumberintheGL00102table.] DECPLACS(DecimalPlaces):[UsedforUnitAccounts]1–02–13–2 TRXTYPE(TransactionType)inGL10000:0–Standard1–Reversing Series(inGLtables):1–All2–Financial3–Sales4–Purchasing5–Inventory6–Payroll–USA7–Project10–3rdParty DTA_Series(in DTAtables):2–Financial3–Sales4–Purchasing10 –Invoicing11–SalesOrderProcessing12–PurchaseOrderProcessing BACHFREQ(BatchFrequency):1–SingleUse2–Weekly3–Biweekly4–Semimonthly5–Monthly6–Bimonthly7–Quarterly8–Miscellaneous PSTGSTUS(PostingStatus)inDTA10100:1–Work2–Open3–History4–Originating(notintheGLyet) ACCTENTR(AllowAccountEntry)inGL00100:0–No(donotallowaccountentry)1–Yes(allowaccountentry) GeneralLedgerreportingnotes: UseACTINDXtolinkaccountnumbers fromtransactions.GL00100willhavetheaccountnameandGL00105willhavetheconcatenatedaccountnumber.Unpostedtransactionsarestoredin2tables(GL10000andGL10001),whereaspostedtransactionswillbeinonetable(eitherGL20000foropenyearorGL30000forclosedyear).GeneralLedgerSQLviews —LastUpdated:March8,2019 Sharethis:EmailPrintTweetLikethis:LikeLoading... 396Responsesto“GL Tables” Alyssa Reply September2,2022at4:07pm HiVictoria, Thankyouforallyourinformationyouhavehere,itisveryhelpful!Iwaswonderingifyoucouldhelpmewithknowingthefieldlinksfortwotables. ThetablesareAccountTransactionandTransactionWorkTable.Iamtryingtofigureoutthecorrectlinkage.Ihavetriedsourcedocumenttosourcedocumentandtrxsourcetotrxsourcebuttheydonotseemcorrect.ThiswasasmartlistwehadbeforethatwasaccidentlydeletedandIamtryingtore-createit. Thepurposeofthereportistoseeallaccounttransactionsposted/unpostedinonespotsoweareabletofilteranduseinsmartview. Best, Alyssa LikeLike VictoriaYudin Reply September4,2022at8:57am HiAlyssa, Pleasetrythis:https://victoriayudin.com/2011/04/27/sql-view-for-all-gl-transactions-in-dynamics-gp/ -Victoria LikeLike BIJoe Reply June10,2020at7:36pm Victoria,I’mtryingtouseGL11110toviewmycurrentbalancesheetbutI’mhavingtroubledetermininghowtofilterforentriesthatarepostedonly.Idon’tseeawaytodothis.Anythoughts? Thankyou LikeLike VictoriaYudin Reply June11,2020at7:13am HiJoe, Thisviewonlyincludespostedentries.Nothingunpostedwillshowuphere. -Victoria LikeLike Ruben Reply September18,2019at8:02am Dothesummarymasterviews(i.e.GL11110)factorinXCHGRATEorCURNCYID? LikeLike VictoriaYudin Reply September18,2019at8:08am HiRuben, Theseviewsshowthedatainfunctionalcurrencyonly. -Victoria LikeLike Rudy Reply May15,2019at11:02am Greattojoinin.VictoriaIamafanandhaveusedyoursiteformeaningfulGPsolutions.IuseGP2015R2 LikeLikedby1person JeffHuelskamp Reply March26,2019at1:38am HelloVictoria, IsitpossibletoseewhatunitaccountsaretiedtoaGLaccount?BasicallyneedtobeabletoqueryoutwhatACCTTYPE(AccountType)1–PostingAccountsarerelatedtowhatACCTTYPE(AccountType)2–UnitAccount. LikeLike MrGarrick Reply January30,2019at1:07pm I’mtryingtofigureoutwhereandhowaPOorReceiptorInvoicegetsassignedtheAccountNumberthatshowsupinGL…ACTNUMBR_1forexampleIhavelookedatitems,lines,headersandvendors.I’mtryingtreflectwhatAccountopenPO’swouldbeasiftheyhadbeenpulledfromdbo_GL11110forexample. LikeLike VictoriaYudin Reply January30,2019at2:35pm Hereismyunderstandingofhowitworks: OnaPOthereareonlyaccountsstoredforinventory: –IftheitemisaninventoryitemthentheaccountnumberistakenfromtheItemaccounts…ifthatisblank,thenfromcompanypostingaccountsunderInventory. –Fornon-inventoryitemstheaccountistakenfromtheVendoraccounts…ifthatisblank,thenfromcompanypostingaccountsunderPurchasing. AlsoimportanttonotethataPurchaseOrdertransactionwillneverget“posted”,soyouwillnotseeanyGLentriesthatcame“fromaPO”. OnaReceipt:itdependsonifit’scomingfromaPOornot.Ifyes,thenit’sfromthePO,ifnot,seeabove.That’sforthelineitems.Alltheotheraccountsaretakenfromthevendorandiftheyarenotthere,thenthecompanypostingaccounts. Hopethathelps, -Victoria LikeLike damiyen Reply August29,2018at5:28pm IwenttoCards>Financial>Accountsandbroughtupanincomeaccount.ItiscategorizedasSales,P&L,withanormalcreditbalance.IclickontheSummaryandthetotalsreflectasignificantcreditbalance,butthenetchangeandperiodbalanceshowasnegative.IwouldexpectanIncomeaccountwithacreditbalancetoshowapositivenetchangeandbalance.Isitpossibletheclienthaditcategorizedincorrectlyatonetime?Forexample,maybetheyhaditcategorizedasExpensewithanormalcreditbalance. LikeLikedby1person VictoriaYudin Reply August30,2018at5:42am HiDamieyn, Netchangeandperiodbalancesforallaccountsarealwaysshownaspositivefordebitsandnegativeforcredits.Thisiscorrectandworkingasitshouldbe.Ifthedisplaychangeddependingonthetypeofaccountortypicalbalancesetup,theuserwouldhavetocheckthiseverytimeandthinkaboutwhatthebalancetheyareseeingmeans,whichcouldresultinalotofuncertaintyandconfusion. -Victoria LikeLike RickD Reply August30,2018at9:31am Thankyou!Thatisveryhelpful. LikeLike damiyen Reply August30,2018at9:32am Thankyou!Thatisveryhelpfulinformation. LikeLike Tavo Reply May25,2018at2:54pm Victoria, I’mpullingfieldsfromGL20000andwonderingifthere’sauniqueidfieldavailablethatIcallpullalso.I’mrefreshingmydatabaseandneedtohaveauniqueidfieldsothatIdon’treplicaterecordsbutrathersimplyrefreshthosethatwerepreviouslypulled.Appreciateanyinsightsyoumighthave. Tavo LikeLike VictoriaYudin Reply May25,2018at3:10pm HiTavo, TechnicallytheDEX_ROW_IDwillalwaysbeunique.However,ifyouarecomparingdatafromdifferenttimes,thereisapossibilitythattheDEX_ROW_ID’swillbechanged,soyoucannotalwaysusethatforcomparison.Normallytheuniquenessofeachrecordwillbedeterminedbythefollowing3fields:JRNENTRY,SEQNUMBR,RCTRXSEQ.TheonlyexceptiontothiswillbetheBBF(balancebroughtforward)entry. So….areyoulookingtocopydatathatisalreadytherefromonedatabasetoanother?IfsoyoucancheckforthecombinationofJRNENTRYandRCTRXSEQ.Ithinkthatshouldworkinyourscenario. -Victoria LikeLike brentpinkston Reply July9,2018at3:27pm Victoria,IhaverowsthatdonothavecombineduniquevaluesofJRNENTRY,SEQNUMBR,RCTRXSEQinGL20000.Shouldthisbepossible? LikeLike brentpinkston Reply July9,2018at3:39pm Ifoundtheissue.Wehavemulticurrency,andwhenIaddtheCurrencyIDtothegroupby,itbecomesunique. LikeLike VictoriaYudin Reply July9,2018at3:39pm Brent,whatistheSOURCDOCofthelineswhereyouareseeingthis? -Victoria LikeLike brentpinkston Reply July9,2018at3:53pm Victoria,themajorityofthemwereBBF(BalanceBroughtForward,andthereasonfortheduplication,Ifoundoutwascurrency.ThereweremultipleCurrencyBalances. LikeLike VictoriaYudin Reply July9,2018at3:56pm Brent, ForBBFtransactions,Iwouldexpectthosevaluestoallbethesame–thisisnormal. -Victoria LikeLikedby1person Kelly Reply May16,2018at2:51pm HiVictoria, I’mtryingtofindouthowthesystemcontrolswhichaccountsareusedwhenpostingSalesOrders.Iseethesales,inv,cogsaccountsonlineitem,however,Idon’tseehowthesystemisdeterminingwhatliability,salestax,andfreightaccountstouse. Wouldyoubeabletopointmewhereinthedatabasethisisdefined? ThankYou LikeLike VictoriaYudin Reply May16,2018at3:11pm HiKelly, Ihaveablogpostonthisthatmayhelp:https://victoriayudin.com/2008/12/08/sales-transaction-entry-gl-distributions-in-dynamics-gp/ Letmeknowifyouhaveotherquestionsthatthisdoesnotanswer. -Victoria LikeLike Rachel Reply December8,2017at11:40am WehavediscoveredthataGLaccountwassomehowsetupincorrectly,andwegetanerrormessagealertingusthatThisisnotaPostingAccount.IcanseeintheGL00100tablethatthisaccountissetupasanACCTTYPE0withPSTNGTYP0.HowcanwecorrecttheAccountTypeandPostingType?Thankyouforanyassistanceyoucanoffer. LikeLike VictoriaYudin Reply December9,2017at5:06am HiRachel, Soundlikesomethingwentwrongwhenyouweresettinguptheaccount.IbelieveACCTTYPEshouldneverbezero–youcanchangethattoa1inSQLandseeifitresolvestheproblem.PSTNGTYPof0isfine–thatmeansBalanceSheet.IfitshouldbeP&LyoucanchangeitonceyoufixtheACCTTYPEandcanaccesstheaccountintheGPapplication. -Victoria LikeLike Rachel Reply December13,2017at10:04am Thankyou! LikeLike Kelly Reply May17,2018at9:41am Greatpost!However,I’mstillnotsurewherethelogicisdefinedtopopulatesomeoftheaccounts.LikeIknowtheitemhassomeoftheaccountsdefinedthere,butthereceivableaccountisnotamongthem. Youcanseethataccountbylookingatthedistributiononthesalesorder,buthowdoesGPknowtousethatspecificreceivableaccount?It’snotliketheuserhastoenterthereceivableaccounteverytimetheycreateasalesorder. LikeLike VictoriaYudin Reply May17,2018at10:57am Kelly, Thereceivablesaccountforsalesinvoicesandreturnsisalwaystakenfromthecustomer.Ifthecustomerdoesnothaveonespecified,it’stakenfromthecompanypostingaccounts. -Victoria LikeLike Kelly Reply May17,2018at4:46pm Wouldyouhappentoknowthedatabasetablethathasthosecompanypostingaccounts? LikeLike VictoriaYudin Reply May18,2018at6:09am Kelly, ThoseareintheSY01100table.IhaveaddedthattomyCompany/Systemtablespage:https://victoriayudin.com/gp-tables/companysystem-tables/. -Victoria LikeLike Kelly Reply May21,2018at1:03pm Igotitalltiedout.ThankYou! LikeLike VictoriaYudin Reply November9,2017at2:28pm HiGreg, Youshouldnormallybeabletousethecreateddate(CREATDDT)inGL00100forthis. -Victoria LikeLike Acacia Reply September28,2017at6:06pm HiVictoria, Isthereawaytojointhefollowingthreetables(usingunionall)tothesaleslinesinSOP30300–HistoricalTransactions(linedetail)? GL10001–TransactionWork/Unposted(linedetail) GL20000–OpenYearPostedTransactions GL30000–HistoricalYearTransactions IamtryingtomakeareportthatclassifyGLentriesbasedonitemnumbers. Isthispossible?Pleaseadvise. Thankyou! LikeLike VictoriaYudin Reply September29,2017at5:49am HiAcacia, ThereisnoreliablewaytodothisbecauseDynamicsGPdoesnotposttotheGeneralLedgerbasedonlineitemsonasalestransactions.Forexample,ifyouhave10lineitemsonasalestransactionwith3differentsalesaccounts,youwillonlysee3salesaccountdistributionsintheGL,not10. -Victoria LikeLike CatherineDouglas Reply September29,2017at7:46am Youcancreateaviewthatwillallowyoutopullthedataandcompare/analyzewhereitemspostedtotheGL: SELECT*FROM –SalesLine FROMSOP30300a –SalesHeader JOINSOP30200cona.SOPNUMBE=c.SOPNUMBEANDa.SOPTYPE=c.SOPTYPE –InventoryAccountNumber LEFTOUTERJOIN(SELECTa.ActIndx,InventoryAcct=a.ActNumSt,b.ActDescr,a.ActNumbr_1,a.ActNumbr_2 FROMgl00105a JOINgl00100bONa.ActIndx=b.ActIndx)fona.InvIndx=f.ActIndx –CostAccountNumber LEFTOUTERJOIN(SELECTa.ActIndx,CostAcct=a.ActNumSt,b.ActDescr,a.ActNumbr_1,a.ActNumbr_2 FROMgl00105a JOINgl00100bONa.ActIndx=b.ActIndx)gona.CSLSIndx=g.ActIndx –SalesAccountNumber LEFTOUTERJOIN(selecta.ActIndx,SalesAcct=a.ActNumSt,b.ActDescr,a.ActNumbr_1,a.ActNumbr_2 FROMgl00105a JOINgl00100bONa.ActIndx=b.ActIndx)hONa.SLSIndx=h.ActIndx WHEREa.SOPTYPEIN(3,4) ANDc.VoidStts<>1 Keepinmindboth3(Invoice)and4(Return)documenttypeshavepositivevaluesinthetables,soyouwillneedtoadjustforDebitandCreditvaluesbasedonwhatentryyouarecomparing. Hopethishelps. LikeLike VictoriaYudin Reply September29,2017at8:06am Catherine, ThissimplyshowsyoutheaccountnumbersdefaultedoneachiteminSOP.Unfortunately,thereisabsolutelynowaytotellwhetherthoseaccountswereactuallyusedontheSOPdocumentwhenitwasposted.ThereisalsonowaytodefinitivelylinktheamountsoftheGLentrylinestotheitemsnumbers.😦 -Victoria LikeLike Di Reply October7,2020at9:03am HiVitoria, IsthereawaytojointheSOP30200orSOP30300withGL20000andGL30000? Ineedtogetthejournalentryforeachtransaction(SOP30200),butcan’tfindawaytojointhesetables. Thankyou LikeLike VictoriaYudin Reply October7,2020at9:08am HiDi, IfyouarepostingindetailandifyoujustwantthejournalentryandnottheactualdetaillineintheGL,thenyoucanlinkonthefollowing: GL.ORDOCNUM=SOP.SOPNUMBEandGL.ORTRXTYP=SOP.SOPTYPEandGL.ORGNTSRC=‘SLSTE’ GLinthiscasecanbeeitherGL20000orGL30000andSOPcanbeeitherSOP30200orSOP30300 -Victoria LikeLike Di Reply October7,2020at11:42am Thankyou. LikeLike Kristie Reply October7,2020at10:40am Question,doesn’tSmartListalreadydothisforyou,usingSeries=SalesandpullingintheOriginatingcolumns,especiallyOriginatingDocumentNumber? LikeLike VictoriaYudin Reply October7,2020at11:04am Yes,SmartListwilldothisforyouintheAccountTransactionsSmartList.However,ifyouwantedtoseeitfromthesalesside,orneeditforacustomreport,thenlinkingitinSQLmightberequired. Also,becarefulaboutusingjusttheoriginatingnumber–thosearenotrequiredtobeunique.ForexampleinSOPyoucanhaveareturnandaninvoicebethesameexactSOPNumber.Sometimespeopledothisonpurposetoshowthatthetransactionsarerelated.Sometimesitjusthappensbecauseofthenumberingschemesthataresetup. -Victoria LikeLike Kristie Reply October7,2020at1:22pm ThankyouVictoria LikeLike Rob Reply August21,2017at4:46pm I’verunaselect*fromgl10110,forsomereasontheaccountnumbercolumnsarecompletelyblank.IstheresomesettinginGPthat’skeepingthedatafrombeingpopulated? LikeLike VictoriaYudin Reply August22,2017at7:13am HiRob, TheACTNUMBR_1,ACTNUMBR_2,etc.columnshavebeenemptyinthistableasfarbackandIcanremember.WhatyouwanttodoislinktotheGL00100orGL00105tableontheACTINDXinGL10110.Thatwillgetyoutheaccountnumberornameorwhateverotherinfoyouneed. -Victoria LikeLike AlexCGlaros Reply August18,2017at12:32am amnewtoaccounting whatdatatablecontainseverysingletransactionregardlessoftype? Notsureifthereissuchatable.Iassumealltransactionsgointoonetablebutarecategorizedandappearinvarious“views”dependingontype. thanks AlexGlaros LikeLike VictoriaYudin Reply August18,2017at4:18am HiAlex, Youareright–thereisnoonetablethatholdseverything.Iftherewas,youwouldseeterribleperformanceintheGPapplication.Everymodulehasmultipletablesfortransactions.Ifyou’rejustgettingintothisandarelookingformoreinformation,therearealotofblogslikethisoneouttheretohelp. -Victoria LikeLike tburrell1014 Reply November3,2016at1:18pm Wearejustrunningafinancialsmartlistandthecolumn“originatingtrxtype”hasdatathatisshowingreversingforourinvoices.Weweretryingtounderstandhowthatcouldbe. LikeLike VictoriaYudin Reply November3,2016at1:22pm Aha.LookslikethatcolumninSmartListisbringinginsomebogusdata.ItISmappinga1inthatdatabasefieldto‘Reversing’,likeyousaid,butincorrectly.IwouldnotusethiscolumninSmartList,asit’snotgoingtohelpyouwithanything. -Victoria LikeLikedby1person Kaley Reply February24,2017at11:31am Victoria-WearetryingtofigureoutwhichGLtabletouseifwewanttofindthedateofthelastGeneralJournalEntrymadeinoneofourcompanies.Doyouhaveanysuggestionsonwhichtabletousetoaccuratelypullthisinformation? Thanks! Kaley LikeLike VictoriaYudin Reply February26,2017at6:46am HiKaley, Areyoulookingfortheactualdatesomeonelastenteredatransactionorthedateofthelasttransactionorsomethingelse?Also,areyoutalkingaboutentriesentereddirectlyintotheGLoranythingthatmayhavebeenpostedinasubledgerthatwenttotheGL?Therearealotofdifferentpossibilities,soifyoucanbeveryspecificaboutwhatyou’relookingfor,Icantrytohelp. -Victoria LikeLike Kaley Reply February27,2017at10:14am HiVictoria, WearelookingfortheactualdatesomeonelastenteredatransactiondirectlyintotheGL. Hopethishelpsclarify. Thanks! Kaley LikeLike VictoriaYudin Reply February27,2017at10:19am HiKaley, Thanksfortheclarification.Unfortunately,GPdoesnottrackthis.YoucansometimesusetheDEX_ROW_TSintheGL10001tableandtheGL20000table,butthatwillstorethelasttimeachangewasmadetothetransaction,notwhenitwasactuallyentered. -Victoria LikeLike tburrell1014 Reply November3,2016at1:02pm Thetransactionoriginatedfromthepayablesmodule.Iwasamtryingtodeterminewhatthewhatthevalueof“1”meansinthefield“ORTRXTYP”intheGL20000table.IassumeditmeantreversingbecausewhenIrunasmartlistandchoosethecolumn“originatingTRXType”itsaysreversing.Whatdoes“reversing”meaninthecontextofaninvoice?Ialsoseeinvoicesthathavenothingandclearingintheoriginatingtrxtypefield. LikeLike VictoriaYudin Reply November3,2016at1:09pm Tamara, Again,you’restartingwithanincorrectpremise.Avalueof1inORTRXTYPintheGL20000tabledoesnotmean‘reversing’.ThevaluesintheORTRXTYPfieldintheGL20000tablearenotanabsolutelist.Theyarerelativedependingonwheretheoriginaltransactionwasentered.IftheoriginaltransactionwasenteredinthePM(PayablesManagement)module,asyouaresaying,a1inORTRXTYPwillmeanpayablesinvoiceanda6willmeanpayablespayment.Thoseshouldcover95%ofyourPMtransactionstypically.Forothermodules,thevaluesinORTRXTYPwillbedifferent.Forexample,fortheSOPmoduleyoushouldnotseeanythingexcept3and4intheORTRXTYP.ForRMtransactions,youwillmostlysee9,butmayseeothers,dependingonhowyouareusingGP. Biggerpicture–whatisitthatyouaretryingtodothatleadsyoutoaskaboutthisfieldinthefirstplace? -Victoria LikeLike tburrell1014 Reply November3,2016at12:48pm Thetransactioncamefromthepayablessubledger.Doyouknowwhatreversingmightmeaninthiscontext? LikeLike VictoriaYudin Reply November3,2016at12:50pm ThereisnosuchthinginPayables.ApayablestransactionwiththeORTRXTYPof1meansaninvoice.YoucanseealltheothervaluesundertheDOCTYPEonthispage:https://victoriayudin.com/gp-tables/pm-tables/. -Victoria LikeLike tburrell1014 Reply November3,2016at12:03pm IlookingattheGL20000tableandthereisafieldcalled“ORTRXTYP”whichistheoriginatingtransactiontype.Themajorityofourinvoiceshavea“1”asthetype.Ibelievethe“1”representsareversingentrybutI’mconfusedastohowthisispossible.Wearen’tselectingtheinvoicestobereversingandtheydon’treversethefollowingmonth.Canyoutellmewhatthisfieldreallyrepresents? LikeLike VictoriaYudin Reply November3,2016at12:40pm TheORTRXTYPisthetransactiontypeofthetransactionintheoriginatingsubledger.Sothevalueswillhavedifferentmeaningsdependingonwherethetransactioncamefrom,whichyoucantypicallyseeintheORGNTSRCandSOURCEDOCcolumns. LikeLike RhysHarris Reply June20,2016at12:06pm HiVictoria WeareusingGP2015R2 IamtryingtomeetMozambiquestatutoryrequirementsbyenteringthegovernmentcodeforeachoftheGLaccounts. IamtryingtodothisusingtheALIASfieldintheFinancial>Cards>Accountwindow Thisfieldwillnotletmeusethesamealiasfortwodifferentaccounts. Anysuggestions? Thanks Rhys LikeLike VictoriaYudin Reply June20,2016at12:15pm HiRhys, IamnotfamiliarwithMozambiquerequirements,however,theAliasmustbeunique,soitdoesnotsoundlikeagoodplacetostorethisdata.Ifallyouneedistostoreadditionaldataforeachaccount,perhapsyoucanuseoneoftheUserDefinedfieldsonthesamewindow?Thereare4ofthemandIrarelyseeallofthemused,sohopefullyyouhaveoneavailable. -Victoria LikeLike RhysHarris Reply July11,2016at5:40am ThanksVictoria,wehaveworkedarounditwithabitofcreativelistmaking🙂 LikeLike PeterYoung Reply August8,2016at5:55pm HiVictoria WedoadailycloseeachdayandposttoourGL.Finally,after15years,thedailypostwasforgottennorwasthisnoticedthefollowingdaysowhenthepostwasdonethefollowingevening,twodaysoftransactionswererolledintoone.Isthereanywaytorectifythis? YoucantellI’mhopelessasIcan’tevenfigureouthowtousethisblogproperly. Thanks Peter LikeLike VictoriaYudin Reply August9,2016at6:41am HiPeter, Unfortunately,your“dailyclose”issomethingspecifictoyourcompany,soit’sprettydifficulttohelpwithyourquestiongenericallyandwithoutalotmoredetail.Whatdoesyourdailycloseentail?Whatexactlywasrolledintooneandhow? -Victoria LikeLike Ben Reply March9,2016at4:03pm Victoria,canyouthinkofanyreasonwhytherewouldbeadifferentposteduserintheGLthanwhatshowsinthePMmodulewhenourPostingSetupinPMissettoPostThroughGeneralLedgerFiles? LikeLike VictoriaYudin Reply March10,2016at2:56pm Ben, Themainreasonisprobablythatsometransactionsdonotauto-postthroughthroughtheGL,eventhoughthat’syoursetting.Forexamplevoids.SotherewillbesomebatchesthatalwaysenduphavingtobepostedmanuallyintheGL. -Victoria LikeLike VictoriaYudin Reply November6,2015at5:01pm HiBeth, Ibelieveit’sinGL00100andcalledClear_Balance. -Victoria LikeLikedby1person Marcia Reply January13,2016at8:13am HiVictoria,Isthereawaytomodifythegeneralledgerentry“description”fieldthroughSQL?Theamountsanddatesarefine,itisjustthenotationthatIwanttochange. Thankyou Marcia LikeLike VictoriaYudin Reply January15,2016at10:18am Marcia, Doyoumeanthefieldcalled“Reference”ontheGeneralLedgerTransactionEntrywindow?Ifso,youcanupdatetheREFRENCEfieldinGL20000(andGL30000)inSQLtochangethat.Ifyoumeansomethingelse,canyoupleaseelaborate? -Victoria LikeLike Beth Reply November6,2015at4:57pm HiVictoria, Isthereatablethatholdsavalueforthenewcheckbox“ClearBalanceDuringYear-EndClose’ontheUnitAccountMaintenancescreen?Wehaveover2300unitaccountsandwouldliketotakeadvantageofthisnewGPfeature. Thanks, Beth LikeLike SherryWhitten Reply October13,2015at6:03pm ThanksVictoria!Iwilltrythat. LikeLike SherryWhitten Reply October12,2015at4:18pm IhaveaquerythatreturnswhatI’mlookingfor–beginningbalance,debit,credit,netchange,endingbalancebyaccountforperiod/year.Myissueisthatifanaccountdoesn’thaveactivityforaperiod,it’snotintheGL10110(orGL10111)table,soitdoesn’tgetarowinmyresultingquery.IneedarowforeachperiodforareportI’mwriting.Solet’ssayattheendofPeriod1,Account12345hasabalanceof$10,000.IthasnodebitsorcreditsforPeriod2.ButIneedarowinmyqueryforPeriod2withBegBal$10,000,Debit$0,Credit$0,NetChange$0,EndBal$10,000. Anyideashowtogetarowforeachperiod/yearevenifaccounthasnodebit/creditfortheperiod?Thanksinadvance!Iuseyourwebsitedaily. Here’smyquery. SELECTTOP(100)PERCENTACTINDX,GPACCOUNTNO,DESCRIPTION,DIV,OFC,SLS,ACT,MDE,FISCALYEAR,FISCALPERIOD,FISCALPERIODNAME,BEGBAL,DEBIT,CREDIT,NETCHANGE,ENDBAL FROM(SELECTB.ACTINDX,RTRIM(B.ACTNUMST)ASGPACCOUNTNO,RTRIM(C.ACTDESCR)ASDESCRIPTION,RTRIM(B.ACTNUMBR_1)ASDIV,RTRIM(B.ACTNUMBR_2)ASOFC,RTRIM(B.ACTNUMBR_3)ASSLS,RTRIM(B.ACTNUMBR_4)ASACT,RTRIM(B.ACTNUMBR_5)ASMDE,A.YEAR1ASFISCALYEAR, A.PERIODIDASFISCALPERIOD,E.PERNAMEASFISCALPERIODNAME, (SELECTISNULL(SUM(PERDBLNC),0)ASExpr1 FROMdbo.GL10110ASD WHERE(ACTINDX=A.ACTINDX)AND(YEAR1=A.YEAR1)AND(PERIODID<=A.PERIODID))–ISNULL(A.PERDBLNC,0)ASBEGBAL,A.DEBITAMTASDEBIT,A.CRDTAMNTASCREDIT,ISNULL(A.PERDBLNC,0)ASNETCHANGE, (SELECTISNULL(SUM(PERDBLNC),0)ASExpr1 FROMdbo.GL10110ASD WHERE(ACTINDX=A.ACTINDX)AND(YEAR1=A.YEAR1)AND(PERIODID<=A.PERIODID))ASENDBAL FROMdbo.GL10110ASAINNERJOIN dbo.GL00105ASBONB.ACTINDX=A.ACTINDXINNERJOIN dbo.GL00100ASCONC.ACTINDX=A.ACTINDXINNERJOIN dbo.SY40100ASEONE.YEAR1=A.YEAR1ANDE.PERIODID=A.PERIODIDANDE.SERIES=0 UNIONALL SELECTB.ACTINDX,RTRIM(B.ACTNUMST)ASGPACCOUNTNO,RTRIM(C.ACTDESCR)ASDESCRIPTION,RTRIM(B.ACTNUMBR_1)ASDIV,RTRIM(B.ACTNUMBR_2)ASOFC,RTRIM(B.ACTNUMBR_3)ASSLS,RTRIM(B.ACTNUMBR_4)ASACT,RTRIM(B.ACTNUMBR_5)ASMDE,A.YEAR1ASFISCALYEAR, A.PERIODIDASFISCALPERIOD,E.PERNAMEASFISCALPERIODNAME, (SELECTISNULL(SUM(PERDBLNC),0)ASExpr1 FROMdbo.GL10110ASD WHERE(ACTINDX=A.ACTINDX)AND(YEAR1=A.YEAR1)AND(PERIODID<=A.PERIODID))–ISNULL(A.PERDBLNC,0)ASBEGBAL,A.DEBITAMTASDEBIT,A.CRDTAMNTASCREDIT,ISNULL(A.PERDBLNC,0)ASNETCHANGE, (SELECTISNULL(SUM(PERDBLNC),0)ASExpr1 FROMdbo.GL10111ASD WHERE(ACTINDX=A.ACTINDX)AND(YEAR1=A.YEAR1)AND(PERIODID<=A.PERIODID))ASENDBAL FROMdbo.GL10111ASAINNERJOIN dbo.GL00105ASBONB.ACTINDX=A.ACTINDXINNERJOIN dbo.GL00100ASCONC.ACTINDX=A.ACTINDXINNERJOIN dbo.SY40100ASEONE.YEAR1=A.YEAR1ANDE.PERIODID=A.PERIODIDANDE.SERIES=0)ASZ ORDERBYGPACCOUNTNO,FISCALYEAR,FISCALPERIOD LikeLike VictoriaYudin Reply October13,2015at4:15am HiSherry, Togetarowforeveryaccountandperiod,youhavetostartwithaquerythatresultsinthat.Kindoflikea‘masterlist’.Maybesomethingthatjoinstheaccountmastertableandthefiscalperiodstablelikethis: select a.ACTINDX, f.YEAR1, f.PERIODID fromGL00100a crossjoin (selectdistinctPERIODID,YEAR1 fromSY40100)f Thenyoucandoaleftouterjointoaddyourexistingquerytothis.Hopethathelps. -Victoria LikeLike May Reply September30,2015at8:42pm IneedawayofuniquelyidentifyingaGLbatchthatcanbeusedbytheendusertotracebacktothatspecificbatchintheUI.InsystemsI’mfamiliarwith,eachGLbatchhasauniquebatchnumber.FromwhatI’mseeinginGP,batchnumbersseemtobereusedforrecurringbatches–i.e.notunique.Isitacombinationoffieldsthatuniquelyidentifyabatch? I’msurethishasanobviousanswer,butI’mstumped. LikeLike VictoriaYudin Reply October1,2015at3:23am May, Youarecorrect.ThereisnothingaboutaBatchIDthatmustbeunique.Evenwithoutrecurringbatches,ausercanre-usethesamebatchIDoverandover.Whydoyouneedthis?Perhapsifyouexplainwhatyou’reultimatelylookingtodosomeonecansuggestadifferentwayofaccomplishingit. -Victoria LikeLike May Reply October1,2015at12:16pm Ihavedevelopedanexternaldatabasetocalculatedeferredrevenue.Eachmonth,thisdatabaseisthesourceoftheJEtorecognisetherevenue(i.e.moveitfromDeferredRevenuetoRevenue).OncetheJEhasbeencreatedthestatusoftherecognisedrecordsareupdatedsotheycannolongerbechanged.IneedawayofconnectingthedetailstotheultimateJE.Inanyotherfinancialsystem,thatwouldbetheGLBatchNumber.WhataremyoptionsinGP? LikeLike VictoriaYudin Reply October1,2015at12:44pm ThanksMay, Thathelpsmeunderstandwhatyou’redoing.YoucertainlycannotusethebatchforthisinGP. Thefollowing4fieldscombinedwillgiveyouauniquerecordinGL20000: OPENYEAR,JRNENTRY,SEQNUMBR,RCTRXSEQ. These4willgiveyouauniquerecordinGL30000:HSTYEAR,JRNENTRY,SEQNUMBR,RCTRXSEQ. However,youwillalsoneedtoexcludeanyrecordswithSOURCDOCin(‘BBF’,’P/L’).Thosearetheyearendclosetransactionsandtheyshouldbeexcludedastheyarenotrealtransactions. Hopethathelpswithwhatyou’relookingtodo. -Victoria LikeLike May Reply October1,2015at2:45pm Thanks,Victoria.Ireallyappreciateyourhelp.AreanyofthosefieldsvisibletotheenduserintheUI? LikeLike VictoriaYudin Reply October1,2015at4:05pm HiMay, Onlythejournalentrynumberandtheyear.Typicallythatshouldbeenough.Iftherearealotofrecurringentries,youcanmaybeaddtheTRXDATE.Unlesstheyhaverecurringtransactionsthatarepostedmultipletimeswiththesamedate(possible,butextremelyunlikely)thatshoulddoit. -Victoria LikeLike May Reply October1,2015at4:29pm Thatwillworksincethisisa‘onceamonth’JE.Thankyou!Youarealifesaver! LikeLike Colleen Reply July6,2015at1:27pm HiVictoria, IamtryingtobuildaSmartListthatwillpullinfofromtheGL00100tableaswellasthesegmentdescriptionfromtheGL40200table.IsthereawaytolinkthesetablesinSmartListBuilder? Thanks, Colleen LikeLike VictoriaYudin Reply July6,2015at1:35pm Colleen, Onceyou’redoinganythingthat’snotplainvanillainSmartListBuilder,ItypicallyrecommendcreatingyourlogicinaSQLview,thensimplypointinganewSmartListtothatview.TolinkGL00100toGL40200youwoulddosomethinglikethis: selecta.*,s.DSCRIPTN fromGL00100a leftouterjoinGL40200s ona.ACTNUMBR_1=s.SGMNTIDands.SGMTNUMB=1 Thatexampleisforthefirstaccountsegment,youcanchangethe1towhateversegmentyou’relookingforonthelastline. Hopethathelps. -Victoria LikeLike Mike Reply August7,2015at1:45pm NotclearhowIcanpostanewquestionsoI’lltrytoaskhere: Isitpossibletoinsertunpostedtransactions(J/Ebatches)directlyintoGL10000andGL10001? LikeLike VictoriaYudin Reply August10,2015at10:51am HiMike, Whileitistechnicallypossibletoinsertnewtransactionsdirectlyintotables,itisstronglynotrecommended.Also,youwilllikelyneedmoretablesthanjustGL10000andGL10001ifyoualsoneedtoinsertnewbatches. Therecommendedapproachistouseanimporttool,suchasIntegrationManageroreConnect,astheywillmaintaintheintegrityofthebusinesslogicbuiltintoDynamicsGP. -Victoria LikeLike Kyle Reply June1,2015at12:54pm HiVictoria, IamattemptingtolinktheAccountTransactionsviewtoacustomer/vendor.IseeIcanusetheOriginatingMasterIDtolinktoeithercustomermasterorvendormastertable,howeverwehavecustomersthatarealsovendorsandtheyhavethesameIDsetupinbothtables.Iamlookingforthebestwaytolinkanaccounttransactiontoeitherthevendororcustomer.I’mthinkingIcanusetheSourceDocumentpossiblytochoosewhetheritwasavendororcustomer,butI’mnotfamiliarenoughwiththeSourceDocumentcodestofigureitout.Justcuriousonyourthoughts.Anyhelpwouldbegreatlyappreciated!. Kyle LikeLike VictoriaYudin Reply June1,2015at1:04pm Kyle, IthinkyoushouldbeabletousetheSeriescolumnforthis–ifit’sPurchasingthenyouhaveavendor,ifit’sSalesthenyouhaveacustomer. -Victoria LikeLike JustinMcNally Reply June30,2015at3:21pm HIVictoria, IamusingGP2015andthebudgettablesyoumentiondonotexistinmydatabase.DoyouhaveanupdatedtablelistforGP2015? LikeLike VictoriaYudin Reply June30,2015at3:36pm HiJustin, IjustcheckedmyGP2015andbothGL00200andGL00201arethere.Notsurewhyyou’renotseeingthem…where/howareyoulooking? Forwhatit’sworth–itwouldbeextremelyunlikelyforMicrosofttoremoveatableonceit’sthere.Eveniftheystopusingit…forlegacysupportandreporting,itwillbekept. -Victoria LikeLike JustinMcNally Reply July6,2015at2:39pm HiVictoria, I’minQueryDesignerinSSRSReportBuilder.Iaminthetablessectionaboveviews,storedprocedureandTable-ValuedFunctions.IseemanyofthetablesyoulistabovebuttheGL00200andGL00201donotshow.Whereelsewouldthesetablesbelisted? LikeLike VictoriaYudin Reply July6,2015at2:42pm HiJustin, Sorry,Idon’tuseSSRSReportBuilder,soIcannotsaywhytheywouldnotshowupthere.Itmaybethatyouruserdoesnothaveaccesstothem,ortheyhavetobeaddedinthereexplicitlytobeavailable?IfyoulookatthelistoftablesdirectlyinSQLServerManagementStudio,youwillseethemthere. -Victoria LikeLike CRuiz Reply May19,2015at5:36pm HiVictoria– MycompanyisusingfieldsecuritytolimitaccesstotheDeletebuttonontheGLtransactionentrywindowinGP2013(SP2).Onlyafewusersareexemptfromit.SomeoftheusersthatarenotexemptneedtouseIntegrationManagertoimportGLtransactionsintothesystem.Whentheytrytoimporttheyreceivethefollowingerror, Openingsourcequery… Establishingsourcerecordcount… Beginningintegration… DOC1ERROR:Macroexecutionerror,Dynamics.DEXTERITY_IM_MACRO_INVISIBLE_ITEM(errorDEXTERITY_IM_MACRO_INVISIBLE_ITEM):ClickHitfield‘DeleteButton’ IntegrationFailed IntegrationResults 1documentswerereadfromthesourcequery. 1documentswereattempted: 0integratedwithoutwarnings. 0integratedwithwarnings. 1failedtointegrate. WehavetriedchangingthetypeoffieldsecurityfromHideFieldtoDisableFieldtoPasswordwithnoluck.Myunderstandingisthatitwillnotworksincewehave“modified”thewindowformandintegrationmanagerisrunningamacro.Theyneedtoruntheseintegrationsonaprettynormalbasis.Doyouknowofaworkaround?IsthereanywaywecankeepthemfromhavingaccesstodeleteGLtransactionsbutallowthemtoimportthem?Anyhelpwouldbeterrific.Thanks!!! LikeLike VictoriaYudin Reply May20,2015at8:31am HiCRuiz, OneideamightbetoupgradetoGP2013R2(ortoGP2015)–whereyoucanpasteGLentriesfroExceldirectlyintoDynamicsGP. AnotherideawouldbetogobacktotheunmodifiedwindowanddisallowdeletingGLentriesforallusers.Insteadofdeletinguserscanvoid.Iftheyreallyneedtodelete,theywouldneedtogotosomeonewhohasaccesstotheGeneralLedgersetupwindowandcanchangethesettingforthemtemporarily. -Victoria LikeLike Algam Reply April9,2015at3:47am DearVictoria, Thisisagreatwebsite.ThankYOUverymuch Ihaveanissue,seriousforme.AsperthefinanceguyTheinventorycostonhandfrommoduleandinventorycostfromgldoesntmatch. CanuPleasetellmehowwegetinventorycostonhandfromGL? (Becauseithinkthefinanceteamismakingsomemistakebytakingthewrongreport.becausethecostdifferenceis35million.) PLEASEHELP… LikeLike VictoriaYudin Reply April9,2015at4:07am Algam, Unfortunately,thereisnooneanswertoyourquestion.TherecouldbeacombinationofmanyfactorscontributingtotheInventorysubledgerbeingofffromtheGeneralLedger.IwouldrecommendeithergettingtogetherwithyourGPPartnertolookthroughyourDynamicsGPtransactionsandseeiftheycanhelpyoufindtheissuesorpurchasingatoollikeTheClosertohelpyoufindalltransactionsthatdonotmatchbetweentheGLandInventory. -Victoria LikeLike Abe Reply March9,2015at5:05pm I’musingthe“SQLviewforeasiergeneralledgertransactionsearches”youcreated,andithasbeenincrediblehelpfulinreviewingsomedataasIdon’tactuallyhaveGP,merelyGPdataloadedontoaSQLServer. ItseemslikeImaybemissingsomeextendedtransactiondata.ThedescriptivefieldsREFRENCEandDSCRIPTNareshort,andIwonderifthereareadditionalnotesfields.Doestheoriginatingdocument(ORDOCNUM)containmore?Iseetwoseries,IAJandDAJ,appearingmostofteninthetransactionsI’minterestedin.Canyoupointmetowardstablesthathavemoredetailforthesetransactions? Thanks, Abe LikeLike VictoriaYudin Reply March26,2015at7:25am HiAbe, REFRENCEandDSCRIPTNarebothamaxof30charactersandthereisnothingmoreonthetransactionitself. IAJandDAJshouldbothbeBankReconciliationtransactions–pleasetakealookatmypagelistingcommonlyusedBankRectablesformoredetail. -Victoria LikeLike TraceyWest Reply March5,2015at10:12am HiVictoria, WewouldliketoreconcilethesubledgersoftheGLbymonthandaccountbacktotheGL.TheapproachwearetakingisGL20000=PM20000+PM30200+RM20101+RM30101+theGeneralJouranalentreisfromGL20000.Wearecloseonmostoftheaccounts,buttherearesomewhichareoffandwearenotquitesurewheretolook.Isthissomethingyouhavetriedbefore,andifsodoyouhaveaquerywecanlookat? Thankyou Tracey LikeLike VictoriaYudin Reply March5,2015at12:26pm HiTracey, Idon’tthinkthatapproachwillwork.HowabouttheReconciletoGLroutine,haveyoutriedthat? -Victoria LikeLike Cecilee Reply March4,2015at1:36pm Victoria, Iranintoasituationwhereweclosedtheyearex:2013,butthenaftertheyearwasclosed,createdayear2012andenteredtransactions.Foundthiswhentryingtoclose2014becauseitistryingtoclose2012,butcannotbecause2013isclosed.Anysuggestionsastohowtofixthis.Notopposedtodeletingthedatain2012thenenteringitasadjustmentsin2013. LikeLike VictoriaYudin Reply March5,2015at7:07am Cecilee, Iwouldrecommendre-openingthe2013year.DependingontheversionofGPyouareon,youcaneitherdothisyourselforyoumayneedMicrosofttodothisforyou.YourGPPartnershouldbeabletohelpyouwiththis. -Victoria LikeLike Kevin Reply February4,2015at5:16pm HiVictoria, I’mnewtoGPandhaveminimumSQLexperience,tryingtogetmyheadaroundthereportingbutitisconfusingandnotveryuserfriendlyasotherfinancialsolutions.I’mtryingtoobtainallmyexpensesassociatedtoanspecificcostcenter.WhattablesshouldIbelookingat?ordoyouhavequerythatcanassistforthisinquiry? Thanks! K LikeLike VictoriaYudin Reply February6,2015at1:36pm HiKevin, Unfortunately,thereisnooneanswertoyourquestionas‘costcenter’isnotasetconceptinGP,it’ssomethingyoudetermine/setup.Also,‘allexpenses’issomethingthatcouldbecalculatedquitedifferently,dependinghowtheyareentered.Ifthisisatypicalquestionyouhavecomeup,IwouldrecommendgettingtogetherwithyourGPPartnerforalittletrainingontheoptionsforpullingupreportslikethisbasedonyourspecificGPsetupandusage. Sorrynottohaveastraightforwardanswer.Oneofthethingsthat’sbothgoodandbadaboutDynamicsGPisthatyoucansetituptotracksomeprettycomplicatedscenarios.It’sgood,inthatyoucandoit.It’sbadinthatit’ssometimesnotsoeasytogetwhatseemslikesimplestuffwhenyouhaveacomplicatedsetup. -Victoria LikeLike StephanieRicks Reply March30,2015at9:57am Kevin, Itdependsontheset-up.Wehavefoursegments,witheachsegmentmeaningdifferentpieces,fromentitywidetojusttheindividualaccounts.OurthirdsegmentoperatesasaDepartment,whichenablesustofiltertransactionsby“costcenter”. SRicks LikeLike Chris Reply September22,2014at11:17am HiVictoria, Whydoesthesamplecompany(Fabrikam)configurethe“typicalbalance”of“accumulateddepreciation”accountstobedebitaccounts?Accumulateddepreciationisacontra-assetaccount.Assetsaredebitaccounts.Contra-assetsarecreditaccounts.IsGPusinganon-standarddefinitionof“debitaccount”and“creditaccount”here? Thanks, Chris LikeLike VictoriaYudin Reply September22,2014at11:32am Chris, OvertheyearsIhavefoundmanyglitchesandissuesinthesamplecompany,soItrytobeverycarefulaboutrelyingonexistingdataintheretobe“correct”.Thisisprobablyoneofthesmallerglitches,sincethetypicalbalancedoesnotreallydriveanyfunctionalityexceptwhatdefaultsforaccountswhenenteringGLtransactions.Thenicethingaboutthisparticularissue,isthatyoucaneasilychangethisyourselfintheaccountsetup.🙂 -Victoria LikeLike RobertF Reply September18,2014at11:49am YoursiteisagreathelpVictoria. IapologizeifthisquestionhasbeenansweredbeforebutIdidn’tfinditwhileIsearchedthroughthesite. IamtryingtoaddtheDistributionReferencetotheGLBatcheditlistbutcannotfinditintheworktable.Isthiskeptinanothertable? LikeLike VictoriaYudin Reply September18,2014at1:50pm HiRobert, ThedistributionreferenceisintheGL10001tableforunpostedtransactions.ThefieldiscalledDSCRIPTN. -Victoria LikeLike TomMurray Reply September16,2014at11:31am IsthereatablethatshowstheuserIDofthepersonwhocreatedaG/Laccount?TheGL00100tableshowedmethedateandtime,butitdidnotshowauserID. LikeLike VictoriaYudin Reply September16,2014at2:45pm Tom, GPdoesnottrackthisdataoutofthebox. -Victoria LikeLike TomMurray Reply September16,2014at3:31pm Thanks,Victoria!Ireallyappreciatealltheusefulinformationyouprovidehere. LikeLike Larry Reply September15,2014at5:52pm HiVictoria, WehaveareportthatshowsallGLAccountsandBalances.Whatwewouldliketodoisonceweclickontheaccountnumber,beabletoseeallthetransactionsthatmakeupthatBalance. IsthereawaytolookatallthedetailedtransactionsthatmakeupaGLbalance? LikeLike VictoriaYudin Reply September16,2014at4:19am HiLarry, Yes,youcanusecodethatdoessomethingsimilartomySQLviewforallpostedGLtransactionsinDynamicsGP. -Victoria LikeLike Doug Reply August18,2014at7:31am Iknowit’saonetimejob,butyoumaywanttocheckoutSmartConnectgoingforwardifyouwillhavemoredataimports.ItisalotfasterthanIntegrationManager. LikeLike MuhammedArifHussain Reply August17,2014at8:18am Ineedtoimport300thousand(3LAC)recordsinCOAinGP2013.IsitcorrectthatIimportitthroughSQLServerinatableinsteadofusingIntegrationManager,becauseIMistakingalongtimetoimportthehugedata.PleaseidentifythetableinwhichIcanImportthis. LikeLike VictoriaYudin Reply August17,2014at8:29am Muhammed, IdonotrecommendimportingdirectlyintoSQLunlessyou(a)knowthetablestructureverywelland(b)havedonealotoftestingwithyourimporttomakesurethattherewillbenoissues.EventhoughIMmighttakealongtimetodothisimport,itisbesttouseanexistingtool. Sincethisisaonetimeimport,onceyou’vedonethisitisover.Ifthisisnotaonetimeimport,Iwouldencourageyoutoreconsider–300,000accountnumbersisalreadyALOT.Anythingmoreshouldbetestedandconsideredcarefully.Whiletechnicallyitwillwork,Iwouldworryaboutperformanceandusabilitywiththismanyaccounts. -Victoria LikeLike MuhammedArifHussain Reply August17,2014at8:43am Thanksforreply. itisrunningwith60recordsinaminutes.Itmeansitwilltake13hoursformyfirst50000recordintegration. Pleasehelp,Ihaveverygoodexp.inSQLServer,Icandothis.IsthereanyothertableIneedtoupdateafterGL00100? Regards, Arif LikeLike VictoriaYudin Reply August17,2014at8:48am Arif, Ihavenotdonethis,so,asIsaid,youwoulddefinitelywanttotestthis,butIbelieveyoualsoneedtopopulateGL00105.Oncedone,youwillneedtomakesuretorunCheckLinksontheFinancialSeriessothatanyotherrelatedrecordsthatareneededcanbecreated. -Victoria LikeLike MuhammedArifHussain Reply August17,2014at8:54am Thanksforgreatsuggestions. Arif LikeLike Kimberley Reply August18,2014at11:13am Youmayalsotrytodivideyourimportintosections.I’vedonesomehugeimportsinthepast(10yearsofhistory),whichtookdaysbythetimeIgotthroughallofthemodules.IfoundthatbreakingthedataintomultiplefilesspeduptheprocessinIntegrationManager. LikeLike WarrenCaulton Reply July3,2014at1:05pm Thanks,ThatisexactlywhatIneeded. LikeLike WarrenCaulton Reply July3,2014at6:24am HelloVictoria, IamtryingtodiscovertheSourceof‘POC’SOURDOCinmyGLtransactions.IsthereaplacewherealltheSOURDOCvaluesareenumerated. LikeLike VictoriaYudin Reply July3,2014at12:34pm HiWarren, IbelieveyoucanseethislistbygoingtoMicrosoftDynamicsGP|Tools|Setup|Posting|SourceDocument. -Victoria LikeLike BrianWalder Reply June11,2014at3:26pm HelloVictoria, ThanksforawealthofGPinformationonyoursite.IamtryingtoqueryinformationfromjournalentriesthatareNOTposted.BasedoninformationIhaveresearched,itseemstheGL10110shouldbethetablewiththedetail,buttherearenocurrentmonthtransactions.Forexample,wehavepostedsalesinvoicesandIamtryingtoutilizetheunpostedGLtransactionscreatedbythesalesinvoicepostingprocessinareportforpreliminarymonthlyrevenuenumbers.Thanksinadvanceforyourinsight. Thanks–Brian LikeLike VictoriaYudin Reply June12,2014at1:17am HiBrian, GL10110isasummarytablethatonlyincludespostedtransactions.UnpostedGLtransactionswillbeintablesGL10000(header)andGL10001(detail).YoucanusemyAllGLTransactionsviewandfilterfor‘Work’transactionstoseethese. -Victoria LikeLike joe Reply June5,2014at10:49am HelloVictoria, Greatsite!IntheGL2000tablewhatisthefieldnamefortheusercreatingajournalentryandthefieldnamefortheuserapprovingthejournalentry? Thanks, -Joe LikeLike VictoriaYudin Reply June5,2014at3:19pm Joe, IbelievebatchapprovalsaregoingtobeintheSY00500table.Onceabatchisposted,theapprovalinformationgoesawayandisnotstoredanywhere.Ifyouneedthat,youwillhavetocomeupwithsomewayofstoringthatinformation.MaybesomethinglikeaSQLtriggerandacustomtablethatyoucanqueryforthedata. IntheGL20000thereisaUserWhoPostedandLastUser.Theoretically,theuserwhocreatedthejournalentrycouldbedifferentthanbothofthose.Soagain,ifyouneedthat,youmayhavetocomeupwithawayofcapturingandstoringthatdata. -Victoria LikeLike CarstenKlausman Reply March6,2014at7:12am Victoria, WearetryingtotaketheGLaccountpostingtoproduceaP/LenrichedwithiteminformationfromtheSOPtables. FirstweareextractingtheGLtransactionsusingthisSQL(Dataset1): SELECTt.ACCOUNTNUM ,t.ACTNUMBR_1 ,t.ACTNUMBR_2 ,t.ACTNUMBR_3 ,t.AmountCur ,t.BUSINESSUNIT ,t.CURNCYID ,t.Department ,t.ORTRXSRC ,t.TRXDATE FROM ( SELECTCAST(LTRIM(RTRIM(l.ACTNUMBR_1))ASNVARCHAR(7))ASACTNUMBR_1 ,CAST(LTRIM(RTRIM(l.ACTNUMBR_2))ASNVARCHAR(7))ASACTNUMBR_2 ,CAST(LTRIM(RTRIM(l.ACTNUMBR_3))ASNVARCHAR(7))ASACTNUMBR_3 ,g.trxdate ,CAST(g.ORTRXSRCASNVARCHAR(25))ASORTRXSRC ,SUM(g.DEBITAMT–g.CRDTAMNT)ASAmountCur ,CAST(g.CURNCYIDASNVARCHAR(5))ASCURNCYID ,CAST(LTRIM(RTRIM(l.ACTNUMBR_1))ASNVARCHAR(10))ASBUSINESSUNIT ,LTRIM(RTRIM(l.ACTNUMBR_3))ASDepartment ,CAST(LTRIM(RTRIM(l.ACTNUMBR_2))ASNVARCHAR(10))ASACCOUNTNUM FROMGL20000g INNERJOINGL00100l ONg.ACTINDX=l.ACTINDX WHEREg.TRXDATE>=‘2011-01-0100:00:00.000’ GROUPBYl.ACTNUMBR_1 ,l.ACTNUMBR_2 ,l.ACTNUMBR_3 ,g.trxdate ,g.ORTRXSRC ,g.CURNCYID UNIONALL SELECTCAST(LTRIM(RTRIM(l.ACTNUMBR_1))ASNVARCHAR(7))ASACTNUMBR_1 ,CAST(LTRIM(RTRIM(l.ACTNUMBR_2))ASNVARCHAR(7))ASACTNUMBR_2 ,CAST(LTRIM(RTRIM(l.ACTNUMBR_3))ASNVARCHAR(7))ASACTNUMBR_3 ,g.trxdate ,CAST(g.ORTRXSRCASNVARCHAR(25))ASORTRXSRC ,SUM(g.DEBITAMT-g.CRDTAMNT)ASAmountCur ,g.[DATAAREAID] ,CAST(g.CURNCYIDASNVARCHAR(5))ASCURNCYID ,CAST(LTRIM(RTRIM(l.ACTNUMBR_1))ASNVARCHAR(10))ASBUSINESSUNIT ,LTRIM(RTRIM(l.ACTNUMBR_3))ASDepartment ,CAST(LTRIM(RTRIM(l.ACTNUMBR_2))ASNVARCHAR(10))ASACCOUNTNUM FROMGL30000g INNERJOINGL00100l ONg.ACTINDX=l.ACTINDX WHEREg.TRXDATE>='2011-01-0100:00:00.000' GROUPBYl.ACTNUMBR_1 ,l.ACTNUMBR_2 ,l.ACTNUMBR_3 ,g.trxdate ,g.ORTRXSRC ,g.CURNCYID )t SecondlythenweareextractingtheSOPtablesusingthisQuery(Dataset2): selectCAST(SOP_LINES.ITEMNMBRASNVARCHAR(50))ASITEMNMBR ,CAST(LTRIM(RTRIM(COA.ACTNUMBR_1))ASNVARCHAR(7))ASACTNUMBR_1 ,CAST(LTRIM(RTRIM(COA.ACTNUMBR_2))ASNVARCHAR(7))ASACTNUMBR_2 ,CAST(LTRIM(RTRIM(COA.ACTNUMBR_3))ASNVARCHAR(7))ASACTNUMBR_3 ,SOP_LINES.QUANTITY ,CAST(SOP_HDR.CUSTNMBRASNVARCHAR(25))ASCUSTNMBR ,CAST(SOP_HDR.TRXSORCEASNVARCHAR(25))ASTRXSORCE ,SUM(SOP_LINES.UNITCOST*SOP_LINES.QUANTITY)ASCostAmoutPosted ,CASE WHENSOP_HDR.SOPTYPE=‘4’THEN(CAST(SUM(SOP_LINES.UNITPRCE*SOP_LINES.QUANTITY)ASNUMERIC(38,9))*-1) ELSECAST(SUM(SOP_LINES.UNITPRCE*SOP_LINES.QUANTITY)ASNUMERIC(38,9))ENDASLineAmount–Returnshastohavethesignchanged ,CAST(SOP_HDR.CURNCYIDASNVARCHAR(5))ASCURNCYID ,SOP_HDR.CCODE ,SOP_HDR.GLPOSTDT FROMsop30300SOP_LINES INNERJOINSOP30200SOP_HDR ONSOP_LINES.SOPNUMBE=SOP_HDR.SOPNUMBE ANDSOP_LINES.SOPTYPE=SOP_HDR.SOPTYPE INNERJOINGL00100COA ONSOP_LINES.SLSINDX=COA.ACTINDX WHERE1=1 ANDSOP_LINES.SOPTYPEIN(3,4)—OnlySalesOrdersandreturns ANDSOP_hdr.PSTGSTUS=‘2’–Onlyposteditems ANDsop_hdr.VOIDSTTS=‘0’–Excludevoidedtransactions GROUPBYSOP_LINES.ITEMNMBR ,coa.ACTNUMBR_1 ,COA.ACTNUMBR_2 ,COA.ACTNUMBR_3 ,SOP_HDR.SOPTYPE ,SOP_LINES.QUANTITY ,SOP_HDR.CUSTNMBR ,SOP_HDR.TRXSORCE ,SOP_HDR.CURNCYID ,SOP_HDR.CCODE ,SOP_HDR.GLPOSTDT Thirdlyweareouterjoiningthedataset1=dataset2bythiskey: TRXDATE=GLPOSTDT ACTNUMBR_1=ACTNUMBR_1 ACTNUMBR_2=ACTNUMBR_2 ACTNUMBR_3=ACTNUMBR_3 ORTRXSRC=TRXSORCE Incaseweareabletojointhetwodatasetswetakethe“Lineamount”fromdataset2andusethatnumbertocalculatetheP/L–howeveritdoesn’tadduptoifweproduceatrialbalancebasedontheGLaccountspostingsonly. WewerehopingtodrillfromaGLaccounttotheactualinvoiceline. IsitatallpossibletolinktheGLpostingstotheSOPtablesandinthatcasewhatarewedoingwrong. Ihopeyoucanputsomelightonourlittlechallenge. KindRegards CarstenKlausman Sr.BusinesssIntelligenceSpecialist PandoraJewellry LikeLike VictoriaYudin Reply March6,2014at7:55am HiCarsten, Therearemanyreasonswhythislogicmaynotwork,thetopfewIcanthinkof: YouhavetransactionsintheGLthatdidnotcomefromtheSOPmodule.Youshouldbeabletoaccountforthewithhowyoujoinyourdata,butIdon’tthinkORTRXSRC=TRXSORCEandtheotherlinksyouhaveatthebottomareenough.Ididn’tgothroughyourcodeinalotofdetail,butIthinkyoushouldbelinkingon(atleast)somethinglikeSOP30300.SOPNUMBE=GL20000.ORDOCNUMandSOP30300.SOPTYPE=GL20000.ORTRXTYP. It’spossiblethattheactualdistributionsonyourSOPtransactionsarenotusingthedefaultedaccountnumbersthatyouareusing(SOP30300.SLSINDX).IfanyoneonevenonetransactionoverridestheGLdistributionsatthesummarylevel,thislogicwillfallapart.TheendresultofthisisthatthereisnotadefinitivelinkbetweenSOPlineitemsandtheGL20000orGL30000tables. Similartothepreviouspoint,butslightlydifferent,soIwantedtoseparateitout,isthatyou’reonlylookingatthequantity*unitprice.Bothofthesemightnotbewhatyouwanttouse,however,thatwilldependgreatlyonexactlyhowyouareusingGP,soit’sverydifficulttospeaktogenericallywithoutlookingatyourdata.Forexample,ifyoueverusemarkdownsortradediscounts,thesewon’tmatchup. So,insummary,dependingonexactlyhowyouareenteringtransactionsintoGPitmaynotbepossibletolinktheGLdirectlytotheindividuallineitemslikeyou’reattempting.WhatIwouldprobablydoinsteadistakeseveralpassesthroughthedata,firstgettheGLdetail,then‘expand’eachGLdetailthatcamefromSOPintowhatlineitemsandaccounts/amountsthatcontains.Youwillneedsomeerrorcheckingheretomakesurethatthetotalsmatchwhat’sintheGLandiftheydonot,atleastshowamessageorcomeupwithsomewaytoshowthatonyourreport. Ihopethathelps. -Victoria LikeLike KimberlyP Reply March5,2014at4:14pm HIVictoria, WhichfieldinGL20000tableisthedatetheuserenteredthedataintothesystem?TRXDATE,DEX_ROW_TSorORPSTDDT] LikeLike VictoriaYudin Reply March5,2014at4:29pm HiKimberly, Iamnotsurethatyoucanalwaysdefinitivelygetthedatesomethingwasentered.ORPSTDDTstoresthedatetheoriginatingtransactionwasposted–notentered.DEX_ROW_TSstoredthedateandtimethelatestchangewasmadetothetransaction.TRXDATEistheGeneralLedgerPostingDate. -Victoria LikeLike TimYork Reply November2,2018at5:51pm HiVictoria, SorrytoreplytosuchanoldpostbutI’vebeenlookingatusingtheDEX_ROW_TSfieldtofindoutwhenaJEwasposted,thinkingthatoncearecordreachedtheGL20000table,itwon’tbesubsequentlychanged.Thus,DEX_ROW_TSshouldreflecttheSQLdate/timetherecordwasinserted.I’mfinding,however,thisnotnecessarilythecaseandthetimestoredcanbelaterthanthetimetherecordwasposted. DoyouknowanymechanismsbywhichGPwouldupdateapostedGL20000record?I’vescriptedallthetriggersinthecompanydatabaseanddon’tseeanyaffectingtheGL20000table(exceptfortheGL20000UtriggerontheGL20000table. Thanksforanyinsightyoucanprovide. -Tim LikeLike LK Reply January17,2014at7:09pm Victoria, Iamtryingtorun select*fromGL00100 whereACTINDXnotin (selectdistinctACTINDXfromGL10001 union selectdistinctACTINDXfromGL20000 union selectdistinctACTINDXfromGL30000) tofindtheaccountsnotusedonanyGLtransactions,butitreturns Msg208,Level16,State1,Line1 Invalidobjectname‘GL00100’ Pleasehelp!Thankyou! LikeLike VictoriaYudin Reply January18,2014at3:58am LK, Soundslikeyoumayberunningyourqueryagainstthewrongdatabase–thiswillonlyworkwhenyourunitagainsttheGPcompanydatabase.🙂 -Victoria LikeLike LK Reply January20,2014at3:37pm ThankyouVictoria.Canyoupleaseclarifywhatyoumeanby“GPcompanydatabase”?IamusingSSMS2008torunthequeryintheDYNAMICSdatabaseontheserverwhereDynamicsGPwasinstalled. Thankyou! LikeLike VictoriaYudin Reply January21,2014at7:59am LK, EachcompanyyouhavesetupinDynamicsGPisaseparateSQLServerdatabase.TheDYNAMICSdatabase(alsocalledthe‘systemdatabase’)holdsoverall/sharedsettings,includingusers,securityandmulticurrency.Alltheotherdata,includingaccountsandtransactions,willbeintheindividualdatabaseforeachcompany.YoucanseealistofyourcompaniesintheSY01500tableintheDYNAMICSdatabase–theINTERIDcolumninthattablewillholdtheSQLServerdatabasenameforeachcompany. -Victoria LikeLike LK Reply January22,2014at3:32pm ThankyousomuchVictoria!Igotitfiguredout.Youarethebest! LikeLike rakesh Reply December16,2013at11:09am hellomam iwanttojointable[GL30000],[GL20000],[GL00100],canupleasetelltherelationshipbetweenthesetable iamtryingtofilterthroughtrxdatefromGL30000table LikeLike VictoriaYudin Reply December16,2013at11:28am Rakesh, SincetheGL20000andGL30000tablescontainsimilardata,youwouldtypicallywanttoUNION,notJOINthem.EitheroneofthemcanbejoinedtoGL00100bytheACTINDXcolumn.YoucantakealookatanexampleofbothoftheseinmycodeforAllPostedGLTransactions. -Victoria LikeLike Abhishek Reply November8,2013at5:00pm ThanksforthereplyVictoria. AlsoIfIwanttolinkPMtable(open/postedtransactions)toGL,whatarethefieldsIshouldjoinon?Ieventuallywanttopullouttherelatedaccountnumbertothetransactions. Thanksagain. LikeLike VictoriaYudin Reply November8,2013at5:07pm Abhishek, YoushouldbeabletolinkfromGL20000totheAPtablesonGL20000.ORCTRNUM=vouchernumber(VCHRNMBR)andGL20000.ORTRXTYP=APdoctype(DOCTYPE). -Victoria LikeLike Abhishek Reply November8,2013at2:24pm HiVictoria, CouldyoupleasetellmewhichcolumninGLtablereferstoCostCenter. LikeLike VictoriaYudin Reply November8,2013at2:25pm HiAbhishek, ‘CostCenter’isnotastandardconceptinDynamicsGP,sothereisnowaytoanswerthisgenerically.ItwoulddependonyourspecificsetupofDynamicsGP. -Victoria LikeLike Darren Reply September24,2013at5:17pm I’vefoundthisinfoveryhelpfulVictoria,sothanksformakingitavailable. I’mextractingdatafromGPandfoundthatIgotdifferentresultsifIinnerjoinedthetransactionstoGL00100vsGL00105ontheACTINDXfield.Iexpectedthetwoaccountmastertablestobeequivalentintermsofaccountindexvalues,butapparentlytheyweren’t.Isitcommonthatthesetwotablesgetoutofsyncwitheachother?Isoneoftheaccountmastertablesmore“reliable”thantheother? Thx Darren LikeLike VictoriaYudin Reply September25,2013at6:31am HiDarren, Thatdoesnotsoundright.These2tablesshouldhavethesamenumberofaccounts,withthesameaccountindexes.Canyourunthefollowingqueriesagainstyourdataandseewhattheycomebackwith? select*fromGL00100 whereACTINDXnotin (selectACTINDXfromGL00105) select*fromGL00105 whereACTINDXnotin (selectACTINDXfromGL00100) -Victoria LikeLike BillWarneke Reply September16,2013at3:06pm HiVictoria,IhaveaquestionaboutjoiningGLandPMtables.I’veseenyourrecommendationthatsuggestsjoiningDocTypetoORTrxTypandVChrNmbrtoORCtrNum,butinmyinstance,thatjoindoesn’twork.WhatI’mlookingforisVendorIDandPOrdNmbrforaGLtransaction.I’vefoundthePO#inPM30200andtheGLTrx#inGL20000.HowdoIequatethem?Thanksforyourhelp!Thisisagreatsite! LikeLike VictoriaYudin Reply September16,2013at3:16pm HiBill, AreyousayingyouwanttoshowthePOnumberfortheGLtransactionscomingfrompayablestransactions?Ifso,therearetwomodulesthatthesecouldbecomingfrom–PMandPOP–andyouwouldprobablywanttocodefortheseseparately(unlessyou’reonlyusingoneortheother).Butifreallydependsonwhatyou’relookingtoreporton.Itmayhelpifyougivemealittlebitmoredetailonwhatyou’relookingtodo…🙂 -Victoria LikeLike BillWarneke Reply September16,2013at4:43pm Yes.Onlyforpayables.Sorry. Ididn’tconsiderthePOPtables….IwillseewhatIfindthere. Thisisforareportthatshowsdetailedaccounttransactionsbyfiscalperiod.Essentially,Ineedtogivethevendorandpo#wherepossible. Bill LikeLike VictoriaYudin Reply September16,2013at5:16pm Bill, Ifyou’reonlylookingatPMtables,youcandosomethinglikethefollowing: selectg.*,p.PORDNMBR fromGL20000g leftouterjoin (selectDOCTYPE,VCHRNMBR,PORDNMBR fromPM20000 union selectDOCTYPE,VCHRNMBR,PORDNMBR fromPM30200)p ong.ORTRXTYP=p.DOCTYPEandg.ORCTRNUM=p.VCHRNMBR -Victoria LikeLike BillWarneke Reply September17,2013at8:50am Thanksforthequery.Itdoeswork.But,anyideawhymostoftheresultsofPOrdNmbrareeithernullorblank?Lessthan1percenthaveavalidPO#. LikeLike VictoriaYudin Reply September17,2013at9:03am HiBill, Thequerydoesnotlimititselftoonlypayablestransactions,Ijustgaveyousomethingtogetyoustarted.Also,nothingexceptaninvoicewillhaveaPOnumberinpayables,andthenonlyasubsetofthemistypicaltoseePOnumbersforinmostcompanies.Ifyou’refindingalotofPMinvoicesdonothavePOnumbersandyouareexpectingthemto,thenyouwillwanttotalktoyouraccountingdepartmentabouthowthesearebeingentered.Hopethathelps. -Victoria LikeLike BillWarneke Reply September17,2013at9:07am Yes.Itdoes.Iwillspeakwiththem. Thanksagainforthehelp. LikeLike Karry Reply September11,2013at6:44pm HowdowerelateourGL2000tablewiththechartofaccountsandtheircategories? Wehave13,757postedtransactionsinGL2000andwhenwetrytorelatethattabletothechartofaccountsGL00100andGL40200sothatwecangroupbyourcategories,weendupwith25,000+records. Victoria,howcanwecreateaSQLquerythatwouldShowusalltheGL2000accountsbutalsogiveustheflexibilitytogroupthembyourcategoriesintheGL40200table? LikeLike VictoriaYudin Reply September12,2013at6:28am Karry, AccountCategoriesareinGL00102.SegmentnamesareinGL40200.BelowisascripttoshowalltheentriesinGL20000andtheircategorynames: selectc.ACCATDSC,g.* fromGL20000g--GL innerjoinGL00100a--accounts ong.ACTINDX=a.ACTINDX innerjoinGL00102c--categories ona.ACCATNUM=c.ACCATNUM -Victoria LikeLike Chev Reply September4,2013at1:33pm HiVictoria, Imanewbietothisandfindyourtablesveryhelpful.ImtryingtofindcurrentbalanceandBeginningbalanceforGLAccounts(GLPGL).Icouldntfindanycolumnswhichdirectlyliststhe,Canyoupleasetellmewheretofindorhowtocalculatethem. Thanks, Chev LikeLike VictoriaYudin Reply September4,2013at2:03pm Chev, Youcan’tfindthisbecauseGPdoesnotstorecurrentorbeginningbalancesintables.Youwouldneedtocalculatethem. -Victoria LikeLike Chev Reply September4,2013at4:58pm ThankouVictoria.. LikeLike Chev Reply September5,2013at11:05am Couldyoupleasetellmeifthereisanystraightforwardwaytocalculatethosebalances?? LikeLike VictoriaYudin Reply September5,2013at4:05pm Chev, Thisgetsmorecomplicatedifyouhavemultipleopenyears,butyoucantrysomethinglikethis: select g.YEAR1[Year], n.ACTNUMSTAccount_Number, g.MNACSGMTMain_Account_Segment, caseg.ACTIVEwhen1then'' else'X'endInactive, g.ACTDESCR[Description], sum(caseg.PERIODIDwhen0theng.PERDBLNC else0end)Beginning_Balance, sum(caseg.PERIODIDwhen0then0 elseg.DEBITAMTend)Debit, sum(caseg.PERIODIDwhen0then0 elseg.CRDTAMNTend)Credit, sum(caseg.PERIODIDwhen0then0 elseg.PERDBLNCend)Net_Change, sum(PERDBLNC)Ending_Balance fromGL11110g innerjoinGL00105n ong.ACTINDX=n.ACTINDX whereg.ACCTTYPE=1 groupbyg.YEAR1,g.MNACSGMT,g.ACTDESCR, g.ACTIVE,n.ACTNUMST -Victoria LikeLike Chev Reply September9,2013at12:46pm Thankyou..Itwasveryhelpful LikeLike Deanne Reply September4,2013at2:13pm HowaboutGL10110(openyear)andGL10111(closedyears)? Thesetableshavebalancesbyyearandperiod. LikeLike VictoriaYudin Reply September4,2013at2:19pm Deanne, AreyoureferringtothePERDBLNCcolumn?Ifso,thoseareactuallynetchangesperperiod,notbeginningorendingbalances. -Victoria LikeLike Cat Reply August28,2013at9:41am Victoria, I’velookedthroughtthesethreadsandcannotfindtheanswerI’mlookingfor.I’mhopingyoucanhelp.I’vebeenaskedtowriteareportthatshowsallGLEntriespostedafter5:00PMontheclosedatefortheclosingperiod. IlookedatGL20000.Dex_Row_TSthinkingthatitwouldhaveonlybeenupdatedwhenarecordwasentered,butfoundthatitisapparentlyupdatedatothertimes.IcannotusetheGL20000.TrxDatebecauseitisthe“posted”dateandnotthedateentered(muchlesstime). DoyouknowofareliablemethodintheGreatPlainsSQLdatabaseforpullingGLrecordsbyentereddate/time? LikeLike VictoriaYudin Reply August28,2013at9:48am Cat, IamnotawareofanythinginGPthatwilltrackthecorrecttime–theDex_Row_TSisascloseasyouwillget,butitisnotalwaysaccurate,asyouhaveseen.YoucangetthedatethetransactionwaspostedinORPSTDDT–thatmaybethebestyoucando.Ifthisisimportantforthefuture,youmaywanttoaddatriggertotrackthisinaseparatetable. -Victoria LikeLike Cat Reply August28,2013at9:59am Thankyousomuchforyourspeedyresponse. Iwillseeiftheywillapprovethetriggerandcustomtable,thenwritethereportusingthat. Iappreciateyou, Cat LikeLike Mark Reply July26,2013at9:51pm oncepostedistheJEreversingdatetrackedaywhereelseIamworkingonauserrequesttoidentifyentriesasreversingwiththereversingdate usingseries=2andortrxtyp=1icanidasreversingbutdon’tseetherevdateinthegl20000orgl30000 LikeLike VictoriaYudin Reply July27,2013at8:17am Mark, Fromlookingthroughourdata,itlookslikethereversingtransactionwillhavethesamejournalentrynumber,buttheRCTRXSEQwillbe1(whereasfortheoriginalentryitwillbe0).AlsothereversingtransactionswillhaveaTRXSORCEstartingwith‘GLREV’. Hopethathelps. -Victoria LikeLike Mark Reply July10,2013at1:23pm HiVictoria, Iapologizeiftheanswerisinherealready.WhenIranyearendcloseandreconcileforacoupledatabases(wehave47databaseswithourinstanceofGP10.0),theperiodid0intheirGL10110andGL10111tablesaremissing.Alltherestofthedatabasesarefine,itisjustforacouplethatwereallsetupatthesametime(doIhaveatriggeraccidentallysettoclearthis–ifso,wherewoulditbe?).Ihavedeletedbothtablesandrebuiltthembutthestartingnumbersaremissing.TherearenomissingentriesinGL20000orGL30000,alltheBBFentriesarethere.TheseBBFarewhatpopulatesperiodid0.Isthereawaytofixthisotherthancreatingthemmanually? LikeLike VictoriaYudin Reply July10,2013at5:07pm Mark, Ihavenotseenthishappenbefore.Doesthismeanthatyouhavenobeginningbalancesforthebalancesheetsaccountsinthesecompanies?Areyouabletorestoreandre-runtheyearendclose?Ifnot,youmaywanttotalktoGPsupportforhelpwiththis. -Victoria LikeLike Larry Reply June3,2013at3:23pm Hello, OurfinancedepartmenthasmanyreoccurringGeneralJournalentries.However,itdoesnotappearthatgreatplainscreatesentriesintheGL10000tablelikeitdoesifyoujustcreateastandardGeneralJournalentry.HowdoesGreatPlainshandletheGeneralJournalEntriesthatarereoccurring?IdoseeahandfulofentriesjustsittingintheGL10000tableasiftheyareaunpostedbatch.DoReoccurringtransactionsalwaysremainasanopenBatchandcanonlybeposted? WhyIaskisthatitstatesthattheGL10000accountistheworkfileforallGeneralJournaltransactions.However,weneverseeanytransactionsgettingcreatedinthisworkfileiftheyartesetasreoccurringtransactions. WhatistheworkflowthroughthetablesforGeneralJournalentries? LikeLike VictoriaYudin Reply June4,2013at5:41am HiLarry, UnlikepostedGLentries,unpostedGLentriesarestoredin2tables:GL10000hastheheaderinformationandGL10001hasthedetail/lineitems.RecurringentriesworkverymuchlikeotherGLentriesandanysavedrecurringGLentrywillbeinbothofthesetables.Wehaveabout5oftheseinourdatabase,andIcanseethemintherealongsidethenon-recurringentries.IntheGL10000tabletheRCRNGTRXfieldwillbe1foranytransactionsthatarerecurring. AnotherwaytolookforthesewouldbetochecktheSY00500table–thiswillcontainallunpostedbatches.Sinceunpostedrecurringtransactionsmustbeinabatch,youshouldseetheminthistablewithaBACHFREQofsomethingotherthan1(singleuse)andaSERIESof2(financial). Ifyou’renotseeinganythinglikethat,Iwouldsuspectthatwhatyourusersarecalling‘reocurring’maynotactuallybeusingtherecurringentryfunctionalityinGP. -Victoria LikeLike DougWilson Reply May30,2013at9:22am HiVictoria, IhavebeengoingtoyoursiteafairbitrecentlyasIamstartingtodomorereportingworkwithGP.Iamstilllearningthetablesandtheirrelationshipssohopingyoucanhelp. IwanttodevelopaquerywhereIcanstartwithg/lpostingsforaspecificaccountnumberandthenjointheira/pvoucherdetails(iftheyexist)andthenthenotesfromtheSY03900table. Iamhavingdifficultyfindingtheprimarykey(s)touseonGL20000andPM32000.Itriedvouchernumber,butgotsomeoddresults.Ialsotriedtheoriginalsource,butthatisabatchreference. WhatcanIusetograbtheGL20000recordsforanaccountandpulltherelatedA/Pdetails? thx, Doug LikeLike VictoriaYudin Reply June1,2013at9:59am HiDoug, YoushouldbeabletolinkfromGL20000totheAPtablesonGL20000.ORCTRNUM=vouchernumberandGL20000.ORTRXTYP=APdoctype.Hopethathelps. -Victoria LikeLike Ana Reply May7,2013at9:05pm HiVictoria,Ineedyourhelp.IsthereaSQLview/scriptthatwillgivemeallGLaccountswithnohistoricalorcurrentactivity.I’dliketotaketheresultsanddeletethoseunusedaccounts;istherealsoascripttodoamassdeletewithinSQLthatyouwouldrecommend? LikeLike VictoriaYudin Reply May10,2013at5:21am HiAna, YoucanusethefollowingcodetofindtheaccountsnotusedonanyGLtransactions: select*fromGL00100 whereACTINDXnotin (selectdistinctACTINDXfromGL10001 union selectdistinctACTINDXfromGL20000 union selectdistinctACTINDXfromGL30000) However,thiswillnotidentifyanyaccountsusedonsetupwindowsforitems,customers,vendors,etc.Oranyaccountsonunpostedtransactionsinthesubledgers.TodoamassdeletefromSQL,youwouldreallywanttomakesurethoseaccountsarenotusedANYWHEREelse.Otherwiseuserswillgeterrorswhenthesystemtriestousethoseaccountsandtheyarenotlongervalid. LikeLike Warren Reply April24,2013at5:20pm HiVictoria, IjuststumbledacrossyourblogandhavefoundmoreinfohereaboutGPthanallotherplacescombined,Iwasthinkingtherewasn’tmuchoutthere! Anyways,IamabitnewgoGPandamgropingaround.IamtryingtolinkitemspurchasedinSOP30300totheGLdistributions(inGL2000?).IseeitemsandtheircomponentsinIV00104,andtheGLACTINDXcolumninIV00101,butthatlookslikethesetuprulesforanitem.IammoreinterestedingettingfromthesaletotheGLsthemoneyendedupin. Thanksinadvanceforanyhelp!IhopeIambeingclear,pleaseletmeknowifclarificationisneededtomakesenseofthis! LikeLike VictoriaYudin Reply April25,2013at8:46am HiWarren, TheaccountdistributionsthatgototheGeneralLedgerwhenanSOPtransactionispostedarenotstoredonalineitembasisandSOP30300isthelineitemdetail…soitmaynotbepossibletoalwaysdefinitivelygetthisatthelineitemlevel.Ifyou’reokwithlookingattheGLdistributionsattheSOPtransactionlevel,theyarestoredintheSOP10102table.Incaseyouneedthedistributiontypes,arelistedonmySOPtablespage.Hopethathelps.Ifyouhavemorespecificquestions,letmeknow. -Victoria LikeLike Jubline Reply March21,2013at7:09am HiVictoria Whatagreatsite,thankyousomuchforprovidingthis.IamnewtoGPandthishasreallyhelpedtoguidemethroughthedatabase:). Ihaveaquickquestion.IsitpossibletolinktheGLtransactionstothePOPtablestoreturntheItemandPOnumber(s)whichcorrespondtotheGLTransaction?Ihavetriedvariouscombinations,butastheinvoicescreatedareoneReceipttomanyPO’sIamstrugglingtogetacorrectmatch. Manythanks. LikeLike VictoriaYudin Reply March21,2013at7:19am HiJubline, Thankyouforyourkindwords. ItmaybepossibletolinktheGLtransactionsonthePOPtablesandtheitem(s)/POnumber(s),however,youmightnotbeabletodoitbyamount.Andyouwillhavetosomehowmanagemultipleresults…sodependingonexactlywhatyouwanttoshowthismaybepossible,butcertainlynoteasy.Partoftheissueiswhatyoumention–onereceiptcouldrepresentmultiplePO’s.AnotherpartwouldbethatitemsarenotnecessarilylinkedtoGLdistributionsonthePOPtransactionsbecausetheGLdistributionsarestoredinsummary,notbylineitemorcorrespondingPOnumber. IfyoucannarrowyourscopetoonlyincludecertaintypesofGL(orPOP)transactionsthatmaybeeasier.Whatspecificallyyouaretryingtoaccomplishwithyourreporting? -Victoria LikeLike Jubline Reply March21,2013at8:02am HiVictoria Manythanksforsuchaspeedyresponse–it’ssokindofyou. Iamtryingtolink2typesofGLtransactionstotheircorrespondingPOnumberandItemnumberssothatwecanfollowPPV(PurchasepriceVariance)throughthesystem. Theissueseemstobethatwhilstmostofthegoodsreceivedmayhaveaone-to-onerelationshipwiththePRGNnumber,atthepointthattheGLInvoiceisraisedandaPurchaseInvoiceputontothesystem,theGLLinePRGNhasmanyPO’sanditems. TheonlynaturalkeybetweenGLandPOPseemstobethePRGNnumber.IamcurrentlylinkingtheGLTransactionViewtoGL00105onACTINDXandthentoPOP30310: GLTransaction_View.ORDOCNUM=POP30310.POPRCTNM thentoPOP10500: GLTransaction_View.ORDOCNUM=POP10500.POPRCTNM Thisisquitepossiblyincorrect…IgotmyideafrompickingthePOP_PPVviewapartandlookingatthetablesusedthereandthentryingtolinkthemintotheGLview. Anylightthatyoucouldshedonthiswouldbereallygratefullyreceived.Thankssomuchagainforallyourhelp🙂 Justine ps–IthinkI’mtryingtodosomethingsimilartoMark(below): Victoria,Regardingthiscommentmadeearlierandyourresponse:Typically(anddefinitelyinthiscase)GPpostsinaggregate–ifyouhave5linesonareceiptthatallgotothesameaccount,youwillnotsee5separateGLdistributionsforthesameaccountinthesametransaction,youwillseeonelinecombiningthem.SothereisnothingtolinkaGLentryorpartofittoaparticularlinenumberonareceipt. DoyouknowifthereisawaytpostthedetailofthereceiptlineitemstotheGLwithoutthempostinginaggregate.Ifnot,whatwouldyousuggesttogetthedetailoftheselineitemsalongwithpurchasingtobalancetoG/L. LikeLike VictoriaYudin Reply March21,2013at8:33am Jubline, FirstthingthatjumpsoutatmeisthatsincebothPOP30310andPOP10500holdmultipletransactiontypes,attheveryleastyouwouldwanttoaddalinkonthetransactiontype. Iamnotfamiliarwith‘POP_PPVview’and‘thePRGNnumber’,whatarethose? Onesuggestionthatismoreofasetupthing,butthatmightmaketrackingPPVeasieristosetupGPsothatitpoststhePPVtoaseparateaccount(orgroupofaccounts).ThatwayyouonlyhavetochecktheaccountswherePPVgoes,notalltransactions/accounts. Butrealistically,evenifthistypeofreportingcanbedone(andIamstillnotconvincedthatitcan),IamnotsureIcan‘generically’helpwiththis,itmayrequiresomeoneactuallylookingatyourdatatohelpyouwithit. -Victoria LikeLike Jubline Reply March21,2013at8:47am HiVictoria Apologiesfornotsendingyouallthecorrectinformation.PRGNnumbersarethereceiptnumbers: POPRCTNMonthePOPtablesandthenORDOCNUM(andpossiblyORCTRNUMontheGLside). ThePPVviewispossiblysomethingwrittenin-house?ItjustreturnsallofthePPVtransactions. IshalltryaddingalinkontheTransactionTypebetweenPOP30310andPOP10500andseeifthathelpsme. Thankssomuchforallofthesuggestions.Iwilldoasyousuggestandseeifwecangetsomeonetotakealookatthedatahereandmakesomesuggestions. Thanksagain,Ireallyappreciateyourassistance.🙂 Jubline LikeLike deannerasmussen Reply March21,2013at8:42am HaveyoulookedatjoiningtheINVINDXfieldinPOP10100totheACTINDXinGL00100?ThisishowIcangetGLtransactionsdowntothePOinfo. LikeLike kavitha Reply March16,2013at3:25am HiVictoria, Letmeknowwhywegetthiserror:InputvariablecontainsaduplicatejournalentrynumberinGeneralLederinGL10000GL20000. Thisismyoneoftheclientfacingthiserror.Canyoutellmehowcanisolvethis… LikeLike VictoriaYudin Reply March16,2013at5:17am Kavitha, ThatdoesnotsoundlikeatypicalGPerrortome,atleastnotonethatIhaveseenbefore.Ifyouhaveacustomizationor3rdpartyproductinvolved,Iwouldaskthemaboutthis. -Victoria LikeLike kavitha Reply March16,2013at5:30am victoria, Itdoesnotinvolvedany3rdPartyproduct.Butforcustomization…firstiamnotclearwiththis.canyouexplainaboutcustomization. IhaveusedsameDLLfiletoimportexcelfileintomyGP. Letmeknowifyouneedanyfartherinfo….forgivingmehelp LikeLike VictoriaYudin Reply March16,2013at5:35am Kavitha, AsfarasIknowimportingintoGPusinga“DLLfile”isnotsomethingthatissupportedorout-of-the-box.SoIwouldthinkthatisacustomprocess.Theerrormessagesoundslikeyouaretryingtoimportajournalentrynumberthatalreadyexists.Ifthatisnotthecase,IwouldprobablyrecommendstartingwiththedeveloperofthisDLLforgettinghelp. -Victoria LikeLike kavitha Reply March16,2013at5:52am Yesyourcorrectvictoria,Ihaveusedsamedllfileintwodiffsystems…ihaveimportedsuccessfully. MyselfonlydevelopthisDLLfile.IhaveusedthisSPtaGetNextJournalEntrytofindingnextjournalnumber… iamgettingcorrectnextjournalentrynumber.Butiambitconfusingwhyinmyclientsystemnotincrementcorrectly.. LikeLike VictoriaYudin Reply March16,2013at5:59am Kavitha, IwouldrecommendtalkingtoMicrosoftSupportforhelponthis. -Victoria LikeLike Kevin Reply January30,2013at3:23pm HiVictoria, Ihopealliswell.Wehaveanissue.Ourclientwasintheprocessofclosingtheyearforhiscompany.Heranthroughtheyearendclosingprocedures.AfterclosingtheyearInoticedthattherewereabunchoftransactionsthatwerenotposted.Iopenedthefiscalperiodandtheuserpostedtheremainingbatches.AfterdoingsoInoticedinourreportthattheopeningbalanceforanincomeaccountwasnotzero.Theclosingbalancewascarriedforwardtotheopeningbalanceofthecurrentyear.WherecanIconfirmtheopeningbalanceforanaccountinGreatPlains?Isthereawaytofixthis?Thanks. LikeLike VictoriaYudin Reply January30,2013at5:30pm HiKevin, Soundslikethataccountmayhavebeensetupincorrectly.HereisalinktoaKBarticleonhowtofixthis:https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;en-us;864913 -Victoria LikeLike SameerAhmed Reply January25,2013at3:40pm HiVictoria, Notsureifthisquestionisrelatedtothisplace.ImaDBAandjusttryingtohelpmycolleagues.Ihopethebelowcanexplainwellwhatwearelookingfor: ThesituationisthatweshowentriestotheSalesTaxliabilityaccountthatshowwhatoursystemthinksweshouldbepayingtotheState.But,whenwepreparethetaxreturnwediscoverdifferences;always.ThisiseitherduetousnotcatchingsomethingonthetaxreturnorourGPsystemiscodingsomekindofsale/salesincorrectlyasataxablesale. Question: Isthereanykindofreportthatcanberunthatshowswhichaccountsinoursystemaresetupastaxable? Maybeifitispossiblewegothroughthetransactionsoftheaccountsthatarechargedwithtaxes? Thanks, Sameer LikeLike VictoriaYudin Reply January25,2013at3:45pm HiSameer, Idonotbelievethereisjustonereportyoucanrunthatwouldhelpyoudeterminewhatiscausingthediscrepanciesyou’reseeing.IwouldrecommendworkingwithyourGPpartnertogothroughyourGPsetupaswellasthereports/windowsyouarelookingatcurrentlythatshowthediscrepanciestodeterminewhatiscausingthem.Whileitcouldbesomethingsimple,itcouldalsobealotofotherthingsanditisreallyimpossibletosaywithoutlookingatyoursystem. -Victoria LikeLike SameerAhmed Reply January25,2013at5:47pm Thankyouforquickreply, Letsassumeifyouhavesysteminfront,whatthingsyou’llcheck. LikeLike VictoriaYudin Reply January26,2013at3:56am Sameer, Iamnotsurewhat‘Letsassumeifyouhavesysteminfront’means,butagain,thereisnowaytoanswerthiswithoutseeingthereportsyouarelookingatandgoingoveryouractualdata.ForallIknowyouhaveGeneralLedgerentriesupdatingthesalestaxliabilityaccountdirectly.Thereisnosystemsettingtocheckforthat–thiswouldrequireunderstandingwherethedataforthoseentriesiscomingfromandwhyitisbeingentereddirectlyintotheGeneralLedger. -Victoria LikeLike Jeff Reply January14,2013at3:07pm RunningGP2010SP1withBinaryStreamMultiEntityManagement. LikeLike VictoriaYudin Reply January14,2013at3:18pm Jeff, Idon’tthinkthiswassomethingnotworkinginSP1ofGP2010,butyou’dhavetolookthroughthelistoffixesforSP2todetermineforsure.It’salsopossiblethattheMEMproductisreplacingthatwindow(asitdoeswithmanywindows)andtheirversionofthiswindowdoesnothavethesamefunctionalityastheGPversion.IfMEMisreplacingthiswindow,youcouldpossiblytestthisbytemporarilydisablingthemodifiedwindowortheMEMproduct…ortalkingtoBinaryStreamsupporttocheckwiththem. -Victoria LikeLike Jeff Reply January14,2013at2:52pm Isthereanactionthatkeepsthesortorder?Therightsideofthelookupscreenkeepsreturningto‘Type’asinpostingorunitetc.ratherthanstayingMainSegment. LikeLike VictoriaYudin Reply January14,2013at3:03pm Jeff, NotthatIamawareof.MineisjustrememberingthelastoptionIchoseautomatically.WhatbuildofDynamicsGPareyouon? -Victoria LikeLike Jeff Reply January14,2013at1:54pm Victoria, IsthereawaytochangetheAccountLookupssorttorunbyaspecifcsegmentratherthantheoutoftheboxsortwhichis‘Type’AFteryouchangetoasegment,letssaymainsegmentforexamplehowdoyoulockthatchoiceinsothatitcomesupwiththesorteverytimeyouopentheaccountslookupscreen? LikeLike VictoriaYudin Reply January14,2013at2:01pm Jeff, GPshouldautomaticallyrememberthese.IjusttestedthisinmyGP2010SP2andthatisthecase.Thisisgoingtobeperuser(possiblypercompany,aswell). -Victoria LikeLike SeanRogers Reply November20,2012at10:33pm HiVictoria, Firstofallthankyouforbloggingextensivelytokeepalloftherestofussane…🙂 MyquestioninvolvesGLtransactions.Hereisthescenario:wetookaclientliveinDynamics2010Septemberofthisyear.ShortlypriortogoliveweimportedalloftheirGLtransactionsfor2012andpostedthem.Withinafewdayswerealizedthatthedatawascorruptandincludedabunchofbatchesthatwerecommitmentsandbudgets.(ThebudgetsandcommitshadbeenpostedtotheGLasactualtransactions) TheobvioussolutionistobackthosebatchesoutoftheGL–thereareonlyafewthousandofthem–butIwaswonderingwhatwouldhappenifwesimplyover-wroteGL20000withthecorrectdataforpre-September,andthenjustimportedthetransactionsfromSeptforwardonward.IfallofthecurrentJEnumberswerethesame,wouldwemaintaindataintegrity?Wewouldn’twanttolosedrill-downandaudittrails. Thisisprobablyastupidquestion–butyouneverknow! KindRegards, Sean LikeLike VictoriaYudin Reply November21,2012at6:15am HiSean, IwouldstronglyrecommendNOTdoingthisdirectlyinthetables.Therearealotofrelatedtablesandyou’regoingtospendalotoftimetesting,checkingandfixingallofthem.Myrecommendationwouldbeimportcorrectingentries. -Victoria LikeLike SeanRogers Reply November21,2012at4:59pm HiVictoria, Thanks,thatiswhatIfigured–butitisalwaysbesttogetasecondopinion… KindRegards, Sean LikeLike deannerasmussen Reply October18,2012at11:37am Iamlookingforatablethatactuallyhasthegeneralledgeraccountbalances–nottheperiodchange–foreachperiod.IamworkingwithGL10110andGL10111.IsthereanyothertablethatjustgivesbalancesoramIstuckhavingtocalculatethisinfo? LikeLike VictoriaYudin Reply October19,2012at10:36am Deanne, GPdoesnotstoretheaccountbalancesanywhere.IfyouneedtheminSQL,theonlywaytogetthemistocalculatethem. -Victoria LikeLike Nish Reply October8,2012at9:35am ThanksVictoria Iwillhavetobuythebook.Ithinkitwilldefintelybeworthit. Regards Nish LikeLike Nish Reply October8,2012at9:06am HiVictoria, Ineedsomeurgenthelpfromyou.WeareintheprocessofmovingacompnayoverfrompastelAccounttoDynamicsGP2010.IneedtoimporttheCOAtogetherwiththreeyearsofhistoricaldata.Pleasecouldyouadviseonthebestprocesstofollowinthisregard. Regards Nish LikeLike VictoriaYudin Reply October8,2012at9:31am Nish, IwouldtypicallyuseIntegrationManagerforthis.Ioutlinetheprocessinmybook. -Victoria LikeLike MyrnaCabanilla Reply September21,2012at3:35am HiVictoria, GoodDay!CouldyoupleasehelpmeinfixingthiserrorweareabouttopostabatchcomingfromSalesTransactionwindowinFinancialBatcheswindowbutthiserrorappear”youcan’tposttranasctioninGeneralLedgerwhilemulticurrencyfortheFinancialSeriesbeingvalued” Thanks, Myrna LikeLike VictoriaYudin Reply September21,2012at8:13am HiMyrna, Soundslikethereisarevaluationprocessstarted,youcannotpostbatchesuntilitiscompleted.Ifnoonehastherevaluationwindowopen,it’spossiblethereisastuckprocessinSQL.Toclearit,makesureallyourusersareoutofGP,thenrunthefollowinginSQL: deletefromDYNAMICS..ACTIVITY deletefromDYNAMICS..SY00800 deletefromDYNAMICS..SY00801 deletefromtempdb..DEX_LOCK deletefromtempdb..DEX_SESSION -Victoria LikeLike MyrnaCabanilla Reply September22,2012at1:00am HiVictoria, Thanksforyourrecommendation. BestRegards, Myrns LikeLike Amanda Reply August27,2012at10:04pm Victoria Thankyousomuch–reallyappreciateyourhelp.You’vesavedmehoursoffrustration.I’msureIspeakforeveryonewhenIsaywe’reallsogladtohavefoundyou. LikeLike Amanda Reply August23,2012at2:49am HiVictoria I’vejustdiscoveredyourblog&amreallyimpressed&grateful.I’mwonderingifyoucanhelp,IaminIT&havebeenaskedtoworkwithourAccoiuntingDeptsoamtryingtogetmyheadaroundGP.Iamtryingtopulltogetherthefollowinginformation: TransactionDate BusinessUnit Department NaturalAccount Amount FinancialYear FinancialPeriod CanyoutellmehowIcanlinktheGL00100,GL00105,GL2000&GL30000inordertopullthisinformationtogether?IcanlinktheGL00100+GL00105tablesbutdon’tknowhowtolinkthemtotheGL20000+GL30000tables. KR Amanda LikeLike VictoriaYudin Reply August23,2012at6:09am HiAmanda, Someofthesethingsarespecifictoyourbusiness(likeBusinessUnit,Department,etc.),soit’sdifficulttoanswerspecifically.Ingeneral,youcanlinkGL20000orGL30000totheGL00100andGL00105tablesbytheACTINDX(accountindex).EachGeneralLedgeraccounthasanindexthatisusedinmosttablesthatyoucanusetosimplifylinking.Therearesometablesthathavemultipleaccountindexeswithdifferentcolumnsnames,butallthetablesyoulistedwillhavejustone–ACTINDX.Hopethathelps. -Victoria LikeLike RonMcVicar Reply August22,2012at3:25pm GLVariableAccountMaitenanceSQLScriptforSSRSeditlist Pleaseprovideinputandthoughts: InaSOXenvironmentgettingaccountantsaccesstoMaintenancescreenstoviewvariableaccountmaintenanceisnotstraightforwardsocreatingaSSRSeditlistforvariableaccountmaintenaceseemsapossibleoptions.ThoseofyouwithmoreunderthehoodSQLexperiencewithGP2010anythoughtsonelementsmissingoranextragarnishmentneeded?Pleasereply: /******VariableAccountMaintenance:GroupInsurance******/ /******Iwasfocusingononevariableaccountyoumighthaveothersorwantall.******/ USE[TEMPDB] ifobject_id(‘#GLVAMSTR’)isnotnulldroptable#GLVAMSTR ifobject_id(‘#GLUAMSTR’)isnotnulldroptable#GLUAMSTR USE[NREG] SELECTDISTINCT RTRIM(GLS.ACTNUMST)ASVAR_ACT, GLV.ACTDESCRASVAR_ACTDISC, RTRIM(GS.ACTNUMST)ASDIST_ACCT, GL.ACTDESCRASDIST_ACDISC, DIST_ACTIVE=CASEWHENGL.ACTIVE=1THEN‘Active’WHENGL.ACTIVE=0THEN‘Inactive’ELSE”END, VA.BDNINDX INTO#GLVAMSTR FROMGL00104ASVA INNERJOINGL00100ASGLONVA.DSTINDX=GL.ACTINDX INNERJOINGL00105ASGSONGL.ACTINDX=GS.ACTINDX LEFTOUTERJOINGL00100ASGLVONVA.ACTINDX=GLV.ACTINDXANDGLV.ACTINDX=17918—Iwasfocusingononevariableaccountyoumighthaveothersorwantall. LEFTOUTERJOINGL00105ASGLSONVA.ACTINDX=GLS.ACTINDXANDGLS.ACTINDX=17918—Iwasfocusingononevariableaccountyoumighthaveothersorwantall. WHEREGLS.ACTNUMSTISNOTNULL SELECT UA.ACTINDXASUAGLINDX, RTRIM(GS.ACTNUMST)ASUNIT_ACCT, GL.ACTDESCRASUNT_ACTDISC, [YEAR]=CASEWHENUA.YEAR1ISNULLTHEN0000ELSEUA.YEAR1END, PERIOD=CASEWHENUA.PERIODIDISNULLTHEN0000ELSEUA.PERIODIDEND, AMOUNT=CASEWHENUA.PERDBLNCISNULLTHEN0.00ELSEUA.PERDBLNCEND, UNT_ACTIVE=CASEWHENGL.ACTIVE=1THEN‘Active’WHENGL.ACTIVE=0THEN‘Inactive’ELSE”END INTO#GLUAMSTR FROMGL10110ASUA INNERJOINGL00100ASGLONUA.ACTINDX=GL.ACTINDX INNERJOINGL00105ASGSONGL.ACTINDX=GS.ACTINDX WHEREUA.ACTINDXIN(SelectBDNINDXFROM#GLVAMSTR) SELECT a.VAR_ACT,VAR_ACTDISC,DIST_ACCT,DIST_ACDISC,DIST_ACTIVE,UNIT_ACCT,UNT_ACTDISC,UNT_ACTIVE,[YEAR],PERIOD,AMOUNT FROM#GLVAMSTRa innerjoin#GLUAMSTRbONa.BDNINDX=b.UAGLINDX ORDERBYa.VAR_ACT,YEAR,DIST_ACCT,UNIT_ACCT,PERIOD LikeLike VictoriaYudin Reply August22,2012at3:50pm HiRon, IthinkIhavesomecodethatisabitmoresimplifiedforthis…givemealittletimetodigitup(andcleanitup)andIwillputupanewblogpostwithit. -Victoria LikeLike SeanReynolds Reply August10,2012at4:41pm Victoria– YourblogisoneofthefirstsitesIcometowhentryingtofigureoutanissuewithDynamicsGP.IhavealwaysfounditEXTREMELYusefulinmyDynamicsGPtravels. IdohaveaquestionabouttheGL10001tablewhileresearchinganissuewithMDA.HowistheDTA_GL_Statuscolumnpopulated?IcurrentlyhaveaproblemwhereMDArecordsare“disappearing”fromtheDTA10100andDTA10200tables.IntheGL10001tableInoticeditsbeenpopulatedwithastatusof0or32,butIdon’tknowwhatthesemean.Doyouhappentoknow? ManyThanks, Sean LikeLike VictoriaYudin Reply August10,2012at5:22pm HiSean, Thankyouverymuchforthekindwords! IcannotfindanyreferencefortheMDA(DTA)tablesintheGPSDK,oranywhereelse.IsubmittedaquestiontoMicrosoftaskingifanythinglikethatisavailable.Inthemeantime,Ididfindthisaboutthefieldyouareaskingabout:http://support.microsoft.com/kb/857570.AlsofoundanothercommentonapartnerforumsayingthattheDTA_GL_Statuswillbe32ifatransactioniscomingfromasubledger.Inourdata,Iseemostofthetransactionswith0comingfromIntercompanytransactions,QuickJournaltransactionsortransactionsliketheyearendclose–basicallytransactionsthatdonotworkwithMDA. Inanycasethough,mygutfeelingisthattransactions“disappearing”fromtheDTA10100andDTA10200tableswouldnotbecausedbywhatisinthatfield.Although,justtomakesureIunderstand,whenyousay“disappearing”–aretheythereatonepoint,thennotthereanymorelater?Ordotheynevermakeitintheretobeginwith? -Victoria LikeLike SeanReynolds Reply August10,2012at5:51pm Thankyoufortheinfo!Ialsofoundthekbyounoted.Itdidn’treallyhelpanswerwhatI’mtryingtotrackdown. Here’sthestepsofwhat’shappening. 1.InformationfromADPisdelivered 2.AccountantimportsthedataintotheFinancial>>GeneralJournalwindowviaTools>>Integrate>>ImportfromADPwhichhethenlinkstoaspreadsheettoimportthedata. 3.Becausewe’reusingMDA(albeitprobablynotcorrectly)heexecutesaMacro(generatedfromVBAcodeinaspreadsheet)toinputtheMDAinformation. Oncehe’sdonethisIcanvalidatethetransactionsintheGL10000,GL100001,DTA10100andDTA10200.Whenhedeletesthebatch,itdeletesthetransactioncorrectlyfromtheGL10000andGL10001tablesandtheDTA10100/200tablesbutitdeletesextrarecordsfromtheMDAtables.Sobeforeheexecutedthistransaction,Ivalidated1061recordsintheDTA10200table,afterhedidtheimport,Ihad1078whichlookedcorrect.Whenhedeletedthebatch,Iexpectedtohave1061,butinsteadhad941,soitdeletedmorethanjusttheMDArecordsassociatedwiththetransaction. Samethinghappenswhenheposts.HecanpostatransactionwithMDAitemsanditwillpostcorrectly,whenhepostsanothertransactionwithMDAitems,itwilldeletethepreviousMDAinformation.It’sthestrangestthingI’veeverseen! ItriedusingtheSupportDebuggingTooltofireoffanemailintheeventofarecorddeletionfromthosetables,butnevergetittofirecorrectly.IranaSQLtracethismorningandcanseeitcallingseveralsp’srelatingtotheprocess(gldeletebatch,dtavalidatestats,etc.)buttryingtofigureout“why”theextrarecordsarebeingdeletedisbecomingquitedaunting. AnythoughtswhereImightstartwiththis?Thegoodthingisthisiseasilyreproducedinourtestenvironmentwhichishelpfulsoit’snotarandomoccurrence. LikeLike VictoriaYudin Reply August13,2012at5:05am HiSean, Thanksfortheadditionaldetail.Thatdefinitelysoundslikethereissomethinggoingwrongandagain,IwouldnotexpectthevalueintheDTA_GL_Statustobetheanswer.Youhave2‘processes’thatarenotquiteout-of-the-box,Iwouldstarttestingbyfirsteliminatingthem,thenaddingthembackoneatatime. Forexample,startbyenteringasampletransactionmanually(doesn’thavetobetheentiretransactionyounormallyimport,justasmallportion),addtheMDAinformationmanually–doyoustillhavethesameresultswhendeletingorposting?Ifnot,thendoanImportfromADPandthenentertheMDAinformationmanually–doyouhavethesameresultswhendeletingorposting? Ifthatstilldoesnothelptrackdowntheproblem,IwouldrecommendworkingeitherwithyourGPPartneriftheyknowtheGPtableswell,orMicrosoftGPSupport.Exitingrecordsbeingdeletedisaprettyseriousissue,andIwouldwanttotrackdownwhatiscausingitassoonaspossible. -Victoria LikeLike Peter Reply August1,2012at5:09pm Victoria,doyouknowwheretheSourceDocumentAPLcomesfrominGL20000?IknowithassomethingtodowithapplyingpaymentsbutIdon’tknowwhatstepsorthroughwhichwindowtheseentriesarecreated. Thanks LikeLike VictoriaYudin Reply August2,2012at6:08am HiPeter, APLstandsforApplyReceivablesDocuments.Whilenotrecommended,itispossibletochangethesourcecodes,butyoucancheckthemforyourcompanybygoingtoMicrosoftDynamicsGP|Tools|Setup|Posting|SourceDocuments. -Victoria LikeLike VictoriaYudin Reply August2,2012at6:11am PS.TypicallyAPLpostingsaregeneratedwhenyouapplyareceivablespayment,returnorcredittoaninvoiceafterpostingusingtheApplySalesDocumentswindowandthereisaGLchangethatneedstoberecorded.Forexample,adiscountorwriteoffistakenorthereisanexchangeratedifference. -Victoria LikeLike Deeann Reply July20,2012at11:42am VIctoria, Thanksforthewebsite.Itissouseful!IamusingGPwithCrystalReportsanddonotseeaGL11110orGL11111onmytablelist.Doyouknowwhythisisorhowtofixit?Thanksinadvanceforyourhelp. Deeann LikeLike VictoriaYudin Reply July20,2012at11:49am HiDeeann, Verygladtohearmyblogishelpful!GL11110andGL11111areviews,nottables.SoifyouareinCrystalandyou’reonlylookingattables,youwillnotseethemlisted.QuickwaytofindingtheseinthelistwhenyouarelookingatthedataconnectionsinCrystal: RightclickonyourconnectionandchooseOptions SelectViewsunderDataExploreranddeselecteverythingelse(or,ifyouarelookingforallGLtablesandviews,leaveTablesandViewsbothchecked,thenintheTablenameLIKEboxtypeinGL%–thatwillgiveyoubothtablesandviewsstartingwith‘GL’) ClickOk RightclickonyourconnectionagainandchooseRefresh Nexttimeyouopenyourconnectionyouwillseeadifferentlist,basedonwhatyouhaveselected Hopethathelps -Victoria LikeLike Deeann Reply July24,2012at9:31am ThanksVictoria!Thatworkedperfectly! LikeLike JeanetteLewis Reply July6,2012at7:09pm IsthereaGP2010tablewherewecanseetheBatchNumberforfinancialtransactionsthathavealreadybeenposted?IamdoingadataqueryfromGP10000forunpostedtransactions,wouldlikesomethingsimilarforpostedtransactions.Anyhelpwouldbeappreicated! LikeLike VictoriaYudin Reply July8,2012at7:26am Jeanette, ThebatchnumberforpostedGLtransactionswillbeintheGL20000(open)andGL30000(historical)tablesinfieldORGNTSRC. -Victoria LikeLike Jerome Reply June19,2012at2:10pm ThanksVictoria!TheyhadtoldmetousethePERIODBALANCEfieldbutwhenitcameupnegativetheysaidthebalancewaswrong.ButasyousaidIthinkitisjustthewaytheyenteredtheinformation. LikeLike Jerome Reply June19,2012at1:16pm Victoria, PleaseexcusemyGPignorance.Someoneaskedmetowriteareportgivingthebalanceonalineofcredit. Wouldntthecreditamountfieldbeequaltothetotalcreditavaliable.Andwouldntthedebitamountfieldbetheamountusedofthecreditline? LikeLike VictoriaYudin Reply June19,2012at1:43pm HiJerome, Noproblematall–Iwasansweringyourquestionfromanaccounting(andGP)perspective.Accounting(andGP)doesnotknowwhatyouareactuallyputtinginaparticularGeneralLedgeraccount.Itcouldbecash,itcouldbesales,itcouldbeanexpense,or…alineofcredit.Nomatterwhat,thebalanceisalwaysDebits–Credits.Thatdoesnotchange. Whatdoeschangeishowtheaccountsareused.Forexample,CashaccountsincreaseontheDebitsideanddecreaseontheCreditside.SoaDebitbalanceforacashaccountmeansapositivecashbalance.However,SalesaccountsincreaseontheCreditsideanddecreaseontheDebitside.Soanegativebalanceforasalesaccountactuallymeanspositivesales.NoneofthisisGPspecific–thisisstrictlyaccounting. Unfortunately,sinceIdonotknowexactlyhowyourlineofcreditentriesarebooked,it’sreallyimpossibletoansweryourquestionanymorespecifically.IcantellyouwhatIthink,orhowImightenterthese,butthatwouldjustbewastingyourtime–youneedtogetthisknowledgefromyouraccountingfolks.So…ifyouarenottoofamiliarwithaccountingandspecificallyhowthisaccountisbeingusedinyourGP,whoeverhasaskedyoutocreatethisreportmayneedtogiveyoualittlemorehelp/guidancethansimplyaskingforabalanceofanaccount. Hopethathelpsexplainthingsjustalittlebetter. -Victoria LikeLike Jerome Reply June19,2012at12:38pm IntheGL11110myperiodbalanceis-6000butmyCreditAmntis20000andmyDebitamntis14000.Isthiscorrect?shoudntmyPeriodBalance=Creditamt–Debitamt? LikeLike VictoriaYudin Reply June19,2012at12:55pm HiJerome, Creditsarenegativeanddebitsarepositive.Andifyou’resubtractingthe2,it’salwaysDebit–Credit.Soyourperiodbalanceis14,000–20,000=-6,000. -Victoria LikeLike Fazil.TK Reply June13,2012at5:10am Haivictoria,hereisanissue:IrantheCheckLinksyesterday.Now,users(inlcludingpowerusers)cannotsee/access/view/createdanyGLaccounts.However,afterlogginginasthesa,theyaredefinitelyallthere.Irunchecklinksofsystemandfinance…theyshownoerror…doyouknowwheretheproblemis…? LikeLike VictoriaYudin Reply June13,2012at6:21am Fazil, IhavenotseenCheckLinksdoanythinglikethis,andwhilenothingisimpossible,Iwouldconsiderthatprettyunlikely.Isitpossiblethatsecuritysettingsand/oruserpermissionsgotchanged? -Victoria LikeLike Fazil.TK Reply June13,2012at6:48am Noo,itsnotchanged.Thisisanewlycreatedcompany.wealsohaveanothertwocompanieswhichisworkingfine.ThesameuserswhocanaccessGLinthistwocompaniescannotaccessinthenewcompany. LikeLike VictoriaYudin Reply June13,2012at6:53am IfyoucreateabrandnewuserandmakethemaPOWERUSER,cantheyseetheaccounts? -Victoria LikeLike MarkGlynn Reply June6,2012at12:00pm Victoria,Regardingthiscommentmadeearlierandyourresponse:Typically(anddefinitelyinthiscase)GPpostsinaggregate–ifyouhave5linesonareceiptthatallgotothesameaccount,youwillnotsee5separateGLdistributionsforthesameaccountinthesametransaction,youwillseeonelinecombiningthem.SothereisnothingtolinkaGLentryorpartofittoaparticularlinenumberonareceipt. DoyouknowifthereisawaytpostthedetailofthereceiptlineitemstotheGLwithoutthempostinginaggregate.Ifnot,whatwouldyousuggesttogetthedetailoftheselineitemsalongwithpurchasingtobalancetoG/L. LikeLike VictoriaYudin Reply June13,2012at6:33am HiMark, IamnotawareofanysettingtochangethepostingbehaviorforPOPreceipts.Theonlyoptionswouldbetopostreceiptsofoneitematatime.Thiswillnotbreakanything,youcanstillenterinvoicesinaggregate,butitwouldcertainlytakealotlongertoprocessreceiptsifyouhavealotofitemsonreceipts. Lookingatiffromthereportingside,youcouldpossibilityusethePOPaccountsanddistributionsfromPOP30310andPOP30390,butyouwouldneedtodosometestingtomakesurethisworksforyoudata. However,ifyou’relookingtodothisbecauseyou’rehavingtroublereconcilingtheInventorymoduleorPayablesmoduletoGL,thenIamnotsurethisisthewaytogobecausePOPonlyhasasubsetofwhatgoesintoInventoryandPayables.SoyouwouldreallywanttocomparetoInventoryandPayablestables,buteventhendoingthismanuallyusingtablesisverycomplicated. Ifthisisarecurringissue,youmightwanttoconsiderTheCloser. -Victoria LikeLike Fazil.TK Reply June13,2012at7:05am No,newusersalsocanntseetheaccounts…hereisasimilarissueifoundfrominternet…http://msgroups.net/microsoft.public.greatplains/cannot-view-enter-any-gl-account/83605#replyForm….butnosolutioninthis. LikeLike VictoriaYudin Reply June13,2012at7:20am You’reprobablybetteroffhavingsomeonetakealookatthis.IwouldrecommendtalkingtoyourGPPartnerorGPSupporttogetthisstraightenedout. -Victoria LikeLike Patrick Reply June4,2012at5:22pm IampullingatransactiondetailfromtheGL10000and100001tablsbutwanttoalsosee(inseperatefields)thesegmentnamesfromtheGL40200table.HowdoIlinkthemtogetherandpullintomyexcelreports.IdontknowwhatfiledstoliktogetherbetweenthetheTrxtables/GL00100/gl00105/gl40200tablestomakethatwork.Anythoughts? LikeLike VictoriaYudin Reply June5,2012at5:13am Patrick, YoucanlinkfromGL10001toGL00100onACTINDX.Onceyou’vedonethat,youanlinkfromGL00100toGL40200basedonthesegmentnumberandvalue,forexample: Forthefirstsegment: GL00100.ACTNUMBR_1=GL40200.SGMNTIDandGL40200.SGMTNUMB=1 Forthesecondsegment: GL00100.ACTNUMBR_2=GL40200.SGMNTIDandGL40200.SGMTNUMB=2 -Victoria LikeLike IKhan Reply May18,2012at12:33pm HiVictoria, IcreatedaSmartListusingSegmentDescriptionMastertogetlistingofallSegments.IsthereawaytogettheUserID(whocreated)andtheCreateDate Thanks, Khan LikeLike VictoriaYudin Reply May19,2012at5:34am HiKhan, GPdoesnotstoreinformationaboutwhocreatedasegmentdescriptionorwhen,sounlessyouweredoingyourowntrackingonthis,Idon’tbelievethisispossible. -Victoria LikeLike IKhan Reply May22,2012at1:31pm Asalways–ThanksfortheInfo –Khan LikeLike David Reply January26,2012at1:12pm Victoria, Thanksforpreviouslyansweringmyquestions,veryhelpful.IhaveidentifiedseveralDEBITSintheRM20101&RM20201tablesthatIamunabletoidentifyitscorrespondingSOPInvoicerecord.TheAPTODCNM=‘DBT00XXXXX’andthe‘APFRDCNM=‘CRT00XXXXX’.Iamnotunderstandingwhatexactlythisdebitsarerelatedto?Aretheydebitingcreditspreviouslyattached?(IhaveconfirmedinthisparticularsystemprefixDBT&CRTareinfactdebits&creditsandconfirmedusingRMDTYPALfield).Anyinsightintothismatterwouldgreatlybeappreciated. –Dave LikeLike VictoriaYudin Reply January26,2012at1:55pm Dave, Thereisnorequirementthatadebitoracreditmemohasto‘relate’toaninvoice.So,thisisnotaGPquestionsomuchasitisaquestionforyouraccountingdepartment.Givethemthespecificdebitandcreditnumbersandaskthem.Iftheysay…’oh,thisgoeswithSOPinvoiceXXXXX’,askthemhowtheyknowthis–istheresomethingtheyhaveenteredintoGPthattellsthemthis?Ifso,whereisit? -Victoria LikeLike Peter Reply January2,2012at11:25pm HiVictoria, Firstofall,LoveyourBlog!Veryinformative!AmlearningalothereandabletoadaptquicklytoGPbecauseofit. Ihaveaquestionre:GP2010,InoticedthattherearetheGL00100F1toF4tables.Iwonderwhatthesearefor.IamfamiliarwiththeGL00100tablesbutwhenItrytoresearchwhatthese“filters”tablesarefor,Ionlyfindtheirdefinitionas“filters”andnothingelsedescribingwhattheyarefor. Thisisoutofcuriosity. Thanks!andMorePower! LikeLike VictoriaYudin Reply January3,2012at5:35am HiPeter, Thanksforyourkindwords! It’sagoodquestion,asIhavenotseenthisdocumentedanywhere.Iamnotsurewhatthosetablesarefor.IsuspecttheywereaddedforsomeperformancegainsandareusedbycertainprocessesinGPasneeded.Ihavenotcheckedthemoften,buthaveneverseenthemhaveanydatainanyoftheGPinstallsIhaveworkedon.Formoreinformation,considerpostingyourquestionontheGPCommunityForumtoseeifanyonecananswerthis. -Victoria LikeLike Peter Reply January3,2012at8:33pm HiVictoria, Thanksforthequickreply.Willtrytoresearchsomemoreonthisalso. Peter LikeLike RaulMendizabal Reply April20,2012at2:02pm Hello,justtoletyouknowthatthesetablesareusedfortheAccountSecurityasignment.EachtablerepresentsoneofthefourlevelsavailablefortheOrganizationalChartusedforAccountSecurityinGP.Thewillholdeachuser’saccountaccess.Bestregards. LikeLike VictoriaYudin Reply April20,2012at5:16pm ThanksRaul! -Victoria LikeLike Peter Reply April22,2012at9:15pm ThanksRaulfortheupdate. –Peter PS:Amlookingformylog-ininformationsinceIchangedlaptops. LikeLike David Reply November21,2011at6:42pm HiVictoria…I’monemonthnewtoDynamicsandsteppedintoanexistingproblem. WearerunningDynamicsGP10withSP3+Hotfixes. Historicallythe“UnappliedAmountTotal”intheInquiry->Sales->ReceivablesSummaryscreenhasalwaysmatchedthe“PeriodBalanceTotal”intheInquiry->Financial->SummaryscreenforourARGL.About14monthsagothePeriodBalanceTotalamountwasoverby$19.74.Thisdifferencehasincreasedthreemoretimesoverthepast14monthsbysimilaramounts.Ihavebeentaskedwithlocatingthetransactionsthatcausedthesedifferencesand/orexplainingwhythetwoamountsnolongerbalance. IhavebeenresearchingtabledefinitionsandlookingatscriptsbutfeelIamjustspinningmywheels.TheonlyemployeewithDynamicsexperienceleftabruptlyabouttwoyearsagoandsincethenemployeeshavebeendoingthingsbyrote,withlittletonoroutinemaintenancebeingperformed. Anyassistanceonwheretostartwouldbegreatlyappreciated! Thankyou. David LikeLike VictoriaYudin Reply November22,2011at8:54am David, HaveyoutriedusingtheReconciletoGLfeatureforthis?(GP|Tools|Routines|Financial|ReconciletoGL)Sometimesthisworksreallywell,othertimesnotsomuch.ButIwouldstartwiththattoseeifithelpstrackdowntheissues. -Victoria LikeLike David Reply November23,2011at7:16pm HiVictoria…IhavetriedrunningtheReconciletoGLfeature,butitcrasheswithaSQLServererrorof: ViolationofPRIMARYKEYconstraint‘PK##1752157′.Cannotinsertduplicatekeyinobject’dbo.##1752157’. Ihaveresearchedtheerrorbuthaveyettofindaworkablesolution.TherewasapparentlyaKB952380articlewithaquerytofixtheerrorbutIamunabletolocateit. Thankyou. David LikeLike VictoriaYudin Reply November24,2011at6:52am HiDavid, IcannotaccessthatKBarticleeither,butotherKBarticleswithsimilarerrorsdonotseemveryhelpful,either.Let’stacklethisfromadifferentangle.FirstIhaveaquestionaboutthedatayouarecomparing.IamnotsurethatIwouldusetheReceivablesSummaryInquirywindowtocomparetoyourGeneralLedgerbalancebecauseifusersareinthesystementeringandpostingtransactionsasyouaredoingthis,youaregoingtobechasingamovingtarget. WhatItypicallydoasastarttotryingtotrackstufflikethisdownisestablishtherealbalancesIamtryingtomatchuphistorically,soIcanseewheretheystartedtohaveanissueandtonarrowdownthedataIneedtosearchthrough.IdothisbyprintingtheReceivablesHistoricalAgedTrialBalance(HATB)reportusingtheGLPostingDateoptionandcomparethattotheGLbalances.Iwouldalsorecommendcheckingperiodends,notcurrentdates.Soforexample,ifyourperiodsarecalendarmonths,printtheHATBasof10/31/11andcomparethattotheGLperiodbalanceasof10/31/11.Arethoseoff?Ifso,gobackuntilyoufindwheretheystartedtobeoff.MakealistofbothbalancesinExcelandcalculatethedifferences.Thiswillhelpestablishwhereyouneedtolookmoreclosely. AnotherconsiderationIwanttomention–unlesssomethingsmallandeasytofindiscausingdifferences,trackingdowna$20differencemaynotbeworthalotoftimeandeffort.Ifyourdifferencesareconsistentlythatsmallandiftheygobackandforth($20onemonth,-$20anothermonth,etc.)thismaybejusttimingdifferencesinsomevoidsandnotreallyanissueworthspendingtimeon.Idon’tknowthesizeofyourtypicalARbalance,butformostcompanies$20isaprettyinsignificantpercentageofthetotal.Ijustwantedtomentionthistoaddalittleperspective.Thatsaid,Ialwaysmakesurethatourbooksreconciletothepenny,butthat’sbecausetheresourceIamusingfortrackingthisstuffdownismeandthisiswhatIdoforalivingandknowitverywell,andfrankly,it’snotverycommonthatoursubledgersdon’treconciletothepennyanyway.🙂 -Victoria LikeLike David Reply November28,2011at4:18pm HiVictoria…Thankyouforthedetailedinformation.FromwhatIunderstand,theyhaveaprocesswheretheyvalidatethetotalsontheReceivablesSummaryInquirywindowandtheGeneralLedgerattheendoftheday,whennooneiselseisinthesystem.Apparentlythetotalsbalancedtothepennyuntilonedaylastyear. Sincetheybalancedaily,theyknowthattheinitialimbalanceocurredbetween9/12/201and9/14/2010.IfoundonerecordinRM10101withadebitamountthatequalsthebalancedifference,butdonotknowhowtovalidateifthisisthetransactioninquestion. Thankyou. David LikeLike VictoriaYudin Reply November29,2011at7:02am HiDavid, Aretheaccountingfolksnotabletolookatthetransactionassociatedwiththisdistributionrecordyoufoundandseewhattheissueis?It’sprettydifficulttoguesswithoutlookingatit.Thetypicalissuesaredatesoraccountnumbers,butitcouldbesomethingelsealso. -Victoria LikeLike Kristie Reply November29,2011at9:05pm IfIcanaddasuggestion–takealookatthetransactions(ifnottoomany)createdbetweenthedatesyounotedandseeifanyofthedistributionshaveanincorrectaccountgoingagainstyourtransactiontypeofRECV.Ithasbeenmyexperiencethatanout-of-balanceiscausedbyusersover-writingtheRECVaccountwithanaccount*otherthan*thecontrolaccountthatshouldbepre-populatedonthattransactionline.GoodLuck! LikeLike David Reply November30,2011at1:30pm HiKristie…Itlookslikewehaveover13,000transactionsforthosethreedays.Ifyoucantellmeknowwhichtables/fieldsIshouldbelookingatIcouldprobablycreateaSQLquerytonarrowthingsdown. Thankyou! David LikeLike Kristie Reply November30,2011at3:33pm Again–merelyasuggestion,noguarantees. Here’swhatIusetofindthetransactionsenteredincorrectlyforReceivables: SELECT*FROMRM30301 WHEREDSTINDX*** andDISTTYPE=3 ***=ReceivablesControlAccountfoundbyqueryingtheGL00100table DISTTYPE3isfortheRECVtransactiontypeImentionedearler Victoria–ifthisisnotthecorrectwayformetouseyourforum,pleasedonothestitatetoletmeknow–Ilovethissitethoughbecausesomanytimesithashelpedmeoutofsomeruts🙂 LikeLike VictoriaYudin November30,2011at6:02pm HiKristie, It’sallgood–Ireallyappreciateyouchimingin! -Victoria LikeLike VictoriaYudin November30,2011at6:11pm IthinkthereplyformmayhavestrippedoutsomeofthecodefromKristie’sreply,itdoesthattomeallthetime.Hereiswhatthequeryshouldlooklike: SELECT*FROMRM30301 WHEREDSTINDX<>*** andDISTTYPE=3 Youcouldalsosearchforthereversecondition: SELECT*FROMRM30301 WHEREDSTINDX=*** andDISTTYPE<>3 -Victoria LikeLike David November30,2011at7:30pm ThanksKristie!Thequerydidlocatetwotransactions,butonewasasaleandonewasareturnsotheyshouldcanceleachotherout. Iamagaingoingtorecommendtheypostanentrytoadjustforthesesmalldifferences.Manyindividualshavespenttoomuchtimetryingtotrackthisdown. Thanksagain!! David LikeLike David Reply November30,2011at12:34pm ThanksVictoria…Unfortunatelytheaccountingfolksaredonotfullyunderstandtheprocessesandarerelyingonmetocorrectissuesthathaveexistedformonths/years.SinceI’manITpersonanddon’thaveanaccountingbackground,Ineedexplanationsonwhythingsaredoneandhowtheywork,whichtheycannotsupply.IthinkIhaveconvincedthemthatthetimeandefforttotrackdownthediscrepanciesisnotworthit. Inowhavetwoadditionalissuestoresearchanddon’tknowwheretostart. Thanksagain! David LikeLike VictoriaYudin Reply November30,2011at2:38pm David, ConsidertalkingtoyourGPPartnertoseeiftheycanhelpyoubridgethatgapbetweenaccountingandIT.Maybeyoucanhavethemdosometraining/troubleshootingforyouthatwillhelpyoulearnGPbetterifyouaregoingtobeexpectedtosupporttheaccountingdepartmentinthisway. -Victoria LikeLike David Reply December28,2011at8:34pm HiVictoria…Itjusthappenedagain.Thistimewecantraceittosomethingthatwaspostedon12/27/2011.TheReceivablesSummaryInquirywas$10lowerthantheGLSummaryInquiryfortheARcashaccount.Bothsummarywindowswereviewedwithnooneelseinthesystem. Isthereanywaytoidentifythedetailedtransactionsbehindeachofthesesummarywindowstolocatetheoffendingrecord(s)? Thankyou. David LikeLike VictoriaYudin December29,2011at5:34pm HiDavid, Iwouldusethequeriesfromthepreviouspostshere.Ijustfixedthemtoshowthecodeproperly.However,basedonourpriorconversation,youmayneedtotalktoyourGPpartnerorGPsupporttohelpyoulookattheresultsanddeterminewhatisactuallycausingthis.Iwouldsuspectthereisacustomerthathasanaccountnumbernotsetupproperly,butbeyondthat,withoutlookingatitIcannottellyoumore. -Victoria LikeLike Rebecca March7,2012at9:44am David,haveyouraccountingfolkslookedatthedefaultARaccountsassignedtoanyofyourcustomers.Thatwouldautomaticallyoverwritethe“normal”ARaccountandcouldcausetheissuethatyou’reseeinghere.Itwouldselfcorrectovertimeifthecustomerrecordischanged. LikeLike Chris March13,2012at11:56pm Hi,I’malsoanITguycurrentlyhelpingouttheAccountsteamwiththesameerror.FromwhatIhaveworkedoutsofarthisseemstobeatemptablewhichisbuilttoholdenquirydatafrommultipletables.Work,Open,andHistoryforRMTransactionsinmycase.IhavenarrowedmyerrortoatransactionthatexistsinbothRM20101(RMOpenFile)andRM30101(RMHistoryFile)withthesameDOCNUMBR.ItwouldseemtheprocesslefttheRM20101recordwhenitshouldhaveremoveditduringposting.Icouldjustremovetherecordbutinstincttellsmenottotouchit.Ihaverunreconcileandchecklinksbutnoerrorsfound.Hasanyonesolvedthisissueyet? LikeLike VictoriaYudin March14,2012at6:14am HiChris, IamalwaysveryconsciousofdataintegrityanddoinganythingdirectlyinsidetheGPtablesunlessIam100%certain.Becauseofthat,IfeelitwouldbeirresponsibletosimplysaythatyoushoulddeletewhatlookslikeaduplicaterecordinRM20101withoutsomeonewhounderstandstheGPtableswelllookingatit.Fromwhatyouaredescribing,mostlikelythatisthesolution,butitalsomaybethattherecordinRM30101isnotcompletelycorrectand/orothertablesmayneedtobecheckedtomakesureeverythingmatchesup,forexampleRM00401,RM20201,RM30201,RM10101andRM30301.Itmaybebesttoaskforsomeonetolookatthiswithyou–maybeyourGPpartnerorGPSupport. -Victoria LikeLike Murad Reply December25,2011at4:24am HiVictoria, IamusingGP10.0,tryingtogetsomedatafromAPmoduletoGLtransactions. KindlypleaseadvisehowIcanlinkAPdetailstoGLtransactions. Anycolumncanbelinkedorthereisotherstepforit. Thanks,Murad LikeLike VictoriaYudin Reply December26,2011at8:58am Murad, YoushouldbeabletolinkfromtheGLtablestothepayablestablesusingthe‘originating’columns.ORMSTRNMwilllinktothevendorID,ORDOCNUMwilllinkthedocumentnumber,ORTRXTYPwilllinktothedocumenttype,andORCTRNUMwilllinktothevouchernumber. -Victoria LikeLike Hishma Reply October17,2011at2:24pm Victoria, DoyouknowwhichtableholdFiscalperiods? Thanks, Hishma LikeLike VictoriaYudin Reply October17,2011at2:35pm Hishma, Thereare2tables:SY40100andSY40101. -Victoria LikeLike Hishma Reply October17,2011at3:28pm ThanksVictoria! LikeLike RoseWysocki Reply October6,2011at6:04pm Hello.Justfoundyoursite.Loveit!IamabitnewtoGPsoanyhelpisappreciated. WearerunningGP10. InthecannedMetric“GrossProfitforthePast12Months”,thefieldforDecember2010isblank.Allothersarepopulatingautomatically.TheliteratureI’vefoundpointstoGL20000,anditappearstohavedataforthatmonth…anyideas? Thanks, Rose LikeLike VictoriaYudin Reply October7,2011at4:53am HiRose, Iamnot100%sure,butIseemtoremembertherebeingabugthatwascausingthisbehavior.Unfortunately,Iamnotfindinganythingaboutthisontheinternet.😦IpersonallyamnotcrazyaboutthisparticularmetricbecauseitisdependentonaccountcategoriesallbeingcorrectandmanycompaniesdonotpayattentiontothatwhencreatingnewGLaccounts.Hereissomemoreinformationonthat:http://www.vaidy-dyngp.com/2010/08/gross-profit-metrics-daouds-article.html. IwouldrecommendpostingyourquestionontheGPCommunityForumtoseeifothershaveseenthisandcanhelpyoufindthecause. -Victoria LikeLike RoseWysocki Reply October7,2011at12:30pm HiVictoria. Thanksforthefastreply.Iusedtheinformationatvaidy-dyngp.comandfoundthedataisthere,perthesmartlistsreferencedtocheckthevaluesinthemetric.Verycoolexercisebutstillhavethe0datainourDec2010bucketonthemetricsgraph,eventhoughit’stherewhenIdothesmartlists.Letmeknowifyouhearanythingelseaboutabug. Thanks, Rose LikeLike Lynor Reply October6,2011at1:54pm HiVictoria, DoyouknowiftheGLAccountRolluphasacorrespondingSQLtablewhereallAccountRollupsthatweresetupinGParestored?IsthereawaytosetuptheAccountRollupInquirytopullGLBalancesfrommultipledatabases?OrevenmaybebyusingtheSmartlistBuilder?Thanks! Lynor LikeLike VictoriaYudin Reply October6,2011at2:10pm HiLynor, AccountRollupshaveabunchofrelatedtables: SE000200 AccountRollupsAccountSegmentIndex SE000300 AccountRollupsOptionsHeader SE000301 AccountRollupsOptions SE000401 AccountRollupsAccountPeriodDetail SE00400 AccountRollupsAccountDetail SE465546 AccountRollupsCalculatedColumn SE810000 AccountRollupsAccountList SE90001 AccountRollupsAccountListAccelerator SE988977 AccountRollupsOptionsColumns ThereisnowaytopullmultipledatabasesintooneAccountRollup,todothatIwouldusuallyuseFRx.Yes,youcanaccomplishthiswithSmartListBuilder,butitmaybesomewhatofachallengetocodethis,asGPdoesnotactuallystorebalances,onlynetchanges,soifyouneededtoseeperiodbalances,youwouldhavetocodethat. -Victoria LikeLike Ron Reply October5,2011at11:47am Victoria, DoyouknowwheretheSalesLineItemDistributionsareheld?IamlookingfortheindividuallineitemdistributionsandIcannotseemtofinditinResourceDescriptions. Ron LikeLike VictoriaYudin Reply October5,2011at12:06pm HiRon, TheyshouldbeinSOP10102.Pleaseletmeknowifthatisnotwhatyou’relookingfor. -Victoria LikeLike VictoriaYudin Reply June9,2011at2:15pm Bab, Dependingonyourpostingsettingsandanumberofotherfactors,transactionsthathavebeenpostedinpayablesmaynowbesittingintheGLwaitingtobeposted.TheywouldbeintheGL10000andGL10001tablesatthisstage.OnlyoncetheGLtransactionsareposteddotheymovetoGL20000.BudgetsareinGL00201,butyoucanalsouseviewGL01201toreportonthem.Hopethathelps. -Victoria LikeLike Vic Reply April8,2011at8:40am HiVictoria Whenweenterasalesorderandapplyapaymentfromacreditcard.IsthereanentrymaneintheGLatthattime? Thanks Vic LikeLike VictoriaYudin Reply April8,2011at8:58am HiVic, Yes,thereshouldbeaGLentryresultingfromthat. -Victoria LikeLike Vic Reply April8,2011at9:04am Victoria IsearchedtheGL10000andGL10001tablesfortheamountoranysognofitandfoundnothing.AnyotherplacesIshouldlook?AlsoitisstillanSOithasnotbeentransferedtoinvoiceyet,doesthatmatter?? Thanks Vic LikeLike FAC Reply April8,2011at9:16am HiVic itwilonlybeinGL10000GL10001whenyouposttheinvoiceinSOPandyoujustwillseethatjournalinGL10000GL10001afteryoupostSOPwhenyourpostingoptionsisunchecked“postthroughGL”insetup/posting/ hopethathelps LikeLike Vic Reply April8,2011at9:19am FAC Thanksyesitdoesit!!!! Vic LikeLike PoojaShah Reply April7,2011at1:16pm HiVictoria, IrecentlyhadanupgradetoGP2010.Priortotheupgradeto2010,IwasabletochangethedescriptionofaninvoicewithintheGL.Thepathwas: Inquiry->Financial->DetailorHistoryDetail->AndthenclickonthelineIwantedtochange Next,I’dgoto: Extras->Additional->MaintainGLRef-> ThenI’dchangethedescription/detailofthatinvoice Idon’tseethe“Extras”optionanylongerandIhaven’tbeenabletofindawaytodothis.Isitstillpossibletoperformthischange?Iappreciateyourhelp. Thanks, PoojaShah LikeLike VictoriaYudin Reply April8,2011at6:50am HiPooja, Soundslikeyouhadacustomizationpriortotheupgrade,aswhatyouaredescribingisnotpossibleout-of-the-boxinGP.CustomizationsneedtobeupgradedseparatelywhenyouupgradeGP,soyouneedtotalktowhoevercreatedthatcustomizationforyouoriginallyandseeiftheycanupgradeitforyou. -Victoria LikeLike Vic Reply April6,2011at10:01am HiVictoria WearelookingforaviewthatshowsALLgltransactionspayables,receivables,banktransactionsandjournalentries. Doyouhaveanythinglikethis??? Thanks Vic LikeLike VictoriaYudin Reply April6,2011at10:51am Vic, Howaboutthisone:https://victoriayudin.com/2009/08/11/sql-view-for-all-posted-gl-transactions-in-dynamics-gp/. -Victoria LikeLike Vic Reply April6,2011at11:01am Victoria Rightonasusual.Isthereanunpostedversionaswell?? thanks Vic LikeLike VictoriaYudin Reply April6,2011at1:11pm HiVic, Ididn’tincludetheunpostedGLtransactions,asIamnotsureifallthesamefieldsareavailableforthem,plusit’stwotablesinsteadofone.Iwilllookintoaddingthoseatsomepointinthefuture.Ifyou’redoingthisyourself,youwillneedtablesGL10000andGL10001. -Victoria LikeLike Vic Reply April6,2011at1:46pm Victoria Thanks!!!IficomeupwithsomethingIwillshootitovertoyou… Vic LikeLike gaganiaro Reply March27,2011at8:51pm HiVictoria, HowdoIknowwhichtransactionsinGL20000areincludedintheperiodofGL10110?LookslikeIcan’tuseTRAXDATEandORPSTDDTtodetermineiftheyfallinthemonthperiodinGL10110. Manythanks, gaganiaro LikeLike VictoriaYudin Reply March28,2011at6:00am Gaganiaro, UsuallyyoucansimplyusetheTRXDATE,however,thereareexceptions.ThefewIcanthinkofoffthetopofmyheadarestufflikebeginningbalancesbroughtforward,adjustingperiodsthatoverlapandnon-calendarmonthfiscalperiods. YoushouldbeabletousetheOPENYEARandTRXDATEfieldsinGL20000togetherwiththefiscalperiodsetupinthecompanytodeterminethecorrespondingperiodinGL10110. IwouldabsolutelyNOTuseORPSTDDT–thatistheactualdatesomethingwaspostedandhasnothingtodowithGLdateswhatsoever. -Victoria LikeLike FAC Reply February15,2011at11:45am HiVictoria PleaseigotaproblemwithsomejournalspostedinGL20000. Igotasalesinvoice(datefebruary2011) thatwaspostedonfeb1st2011. butthattransactiongenerated8journalentriesingl2000 JRNENTRY8isthecorrectdistributionofthesalesinvoiceandTRXDATE=feb1st2011 butJRNENTRY1to7arein30thapril2010,31thmay2010,30thjune2010,31thjuly2010,31thaugust2010,31thdecember2010and31thjanuary2011. Andallthesejournalsdistributionsareverysmallammountsusinginventoryandcostofgoodsaccounts JRNENTRY=8hasORGNTSRC=SLSTE00003908ANDSERIES=3ANDORTRXTYP=3 ANDJRNENTRY1to7hasORGNTSRC=GLTRX088013ANDSERIES=5ANDORTRXTYP=0 Idontknowwhythesejournalswereautomaticallycreated.Theproblemisthattheyhavechangedthedataof2010(theydidntclose2010periods) Couldyouhelpmeplease? isthereawaytosetuppostingthatnotgeneratethisjournals? Thanksalot LikeLike VictoriaYudin Reply February15,2011at1:06pm FAC, Itsoundstomelikejournalentries1through7wereactuallygeneratedbythePOPinvoicesforadjustmentstoinventorycost.AretheyhittingInventoryandCostofGoodsaccounts?UsuallytheREFRENCEforthesetransactionswillbe‘SALES’andthenthePOPreceiptnumber.SoSALES11069willbecreatedfromPOPreceipt#11069. Ithinkthatthedateswillbetheoriginaldatesoftheinventoryreceipts,butIwouldhavetotesttoconfirmthat.Idon’tbelievethereisawaytostopthispostingfromhappening.Thisiswhyitisimportanttolookatthepostingreportsthatcomeoutwhentransactionsareposted.Andofcourse,tocloseperiodsthatshouldnotbepostedto,asyoumention.ThiswouldhavegottencaughtinBatchRecovery,sothedatescouldbechangedasneeded. -Victoria LikeLike FAC Reply February15,2011at2:49pm ThanksVictoria thoseitemsquantitiesareintegratedbyInventoryTransactions(adjustments)byathirdpartymanufacturingsoftware. TheSalesinvoicenumberis002-002949 andtheREFRENCEforjournalentries1to7areIVT002-002949,IVA002-002949,VENTA002-002949allofthemwiththesalesinvoicenumberattheend. Soicannotfindwhichinventoryadjustmentstheyarereferringto. TheonethingicannotunderstandiswhytheyarehittingInventoryandCostofGoodsaccounts?Imeanwhyasalesinvoicehastochangeacostofgoodaccount? Istherewaytoviewapostingreportagain? Thanksalot Ireallyappreciateyourhelp. LikeLike VictoriaYudin Reply February15,2011at3:27pm FAC, IVTisaninventorytransfer,IVAisaninventoryadjustment,butIhavenotseenVENTAbefore… Whatistheinventoryvaluationmethod?Thatmightmakeadifferenceinfindingthese…ifitisFIFOandthisinvoicewasjustposted,youshouldbeabletoseewhatthelastcostlayerssoldwere. Most,butnotallpostingreportscanbereprinted.ForSales,youcangotoReports|Sales|PostingJournals…butIbelievewhatyou’relookingforistheCostVarianceJournal,whichisnotsomethingIseeasavailableforreprinting. IwouldrecommendpostingthisontheGPforumstoseeifyougetsomemorefolksrespondingwhohaveseenthese…IhaveonlyseenthemcomingfromthePOPmodule,notfromsales. -Victoria LikeLike FAC Reply February15,2011at3:33pm Thankyouverymuch! LikeLike Divya Reply February11,2011at9:01am HiVictoria, IhaveajournalentryinGL20000.Ithas, SOURCDOC:GJ. REFRENCE:SALESC123555 BCHSOURC:GL_Normal SERIES:5. BACHNUMB:GLTRX441006 ItriedtracingthisjournalinInquiry,butitshowsamessage“TransactionHistorydoesnotexistforthistransaction”.InGL10000,somejournalsareavailableforORTRXSRC:GLTRX441006.(sameasGL20000’sBACHNUMB)andthesejournal’sSOURCDOC,BCHSOURC,SERIESaresameasmentionedabove.Idontknowforwhichscenarioandwhythesejournalsarecreated.Pleasehelp. Thanksinadvance. LikeLike VictoriaYudin Reply February11,2011at10:45am HiDivya, ThissoundslikeaninventorycostadjustmentthatgotposteddirectlytotheGLfromaPOPinvoice.Iwouldexpect‘C123555’tobeyourPOPReceiptNumberfortheinvoice.Thetypicalscenarioissomethinglikethis: Postreceiptofinventory. Selltheinventoryandposttheinvoice. Enterthevendorinvoicefortheinventory,matchittothereceiptandchangetheprice. ThedifferenceintheinventorycostforwhatwasalreadysoldwillbeposteddirectlytotheGLinatransactionlikeyouaredescribing. Hopethathelps, -Victoria LikeLike Yolanda Reply February10,2011at2:04pm HiVictoria, Wearetryingtoperformtheyearclosingforoneofourentitiesandwereceiveamessagethatsaystheretainedearningsaccountisinactive.IhavecheckedtableGL00100inSQLandalloftheretainedearningsaccountsarecodedwitha1.Ialsolookedattheaccountitselfunderthemastercardrecordsandtheyarecodedasactive.Haveyouseenthismessagebefore? LikeLike VictoriaYudin Reply February10,2011at3:53pm Yolanda, Ihavenotseenthisbefore.AreyouclosingtojustoneRetainedEarningsaccount?Ordoyouhave‘ClosetoDivisionalAccountSegments’checked? Haveyourecentlymadeanychangestoyouraccounts?HaveyoutriedrunningCheckLinksorReconcileontheFinancialseries? -Victoria LikeLike Akram Reply January25,2011at11:09am Hivictoria, IamrunningGP10SP3+Hotfix(standardmodules+AnalyticalAccounting) AsatestihaveperformedayearendinFabrikam. afteryearendiverifiedtheBBALdetailsbetween SummaryinquiryScreenandDetailinquiry… thedatedonotmatchwhyisitso…butforearlieryearsitmatchesExactly. Example: CashOperatingAccount :000-1100-00 BBAL Debit :1529714.84 Credit :921077.53 onclickingdetailsofthesameBBalPeriod Detailinquiryscreendisplaysfollowings CashOperatingAccount:000-1100-00 Year:2015 Date:31-12-2014to31-12-2014 JVNo :3460 Debit :608637.31 Credit :0.00 Canyouletmeknowwhythisisnotmatching????? Sameisthecasewithmylivecompanytooo…. Thanks Akram LikeLike VictoriaYudin Reply January25,2011at11:16am Akram, ThismayjustbeanissuewithhoeGPdisplays/storesthedata.JusttomakesureIunderstand,canyoupleaseletmeknowthefollowing: 1.GotoInquiry|Financial|HistorySummaryandbringuptheaccountandthelastclosedyear(2014inyourexample),whatisthePeriodBalance(lastcolumn)ontheLASTline? 2.GotoInquiry|Financial|HistorySummaryandbringupthesameaccountforthefollowingyear(2015inyourexample),whatisthePeriodBalance(lastcolumn)ontheFIRSTline? -Victoria LikeLike Akram Reply January25,2011at11:29am HiVictoria, Thanksforinstantreply 1.GotoInquiry|Financial|HistorySummaryandbringuptheaccountandthelastclosedyear(2014inyourexample),whatisthePeriodBalance(lastcolumn)ontheLASTline? 2.GotoInquiry|Financial|CurrentYearSummaryScreenSummaryandbringupthesameaccountforthefollowingyear(2015inyourexample),whatisthePeriodBalance(lastcolumn)ontheFIRSTline? TheperiodBalancearesameonbothscreenwithlastlinematchingthefirstline… 608637.31USD Inlivetestingscenario… Thetrailbalancebeforeandaftermatchescompletely,alsotheretainearningaccountsbalanceiscorrectasperacctdept. itsonlythatthistwoscreendonotmatcheachother. Isthereanyspecificreasonforthis…. -Akram LikeLike VictoriaYudin Reply January25,2011at11:35am HiAkram, Thismaybebecauseyoupostedadditionaltransactionstotheclosedyearaftertheyearwasclosed.OryoumaybelookingattherepresentationofhowGPstorestheyear-endclosingentry.Withoutseeingyourdataoratleastsomescreenshots,itisdifficulttotellexactlywhatthereasonisforwhatyouareseeing.However,iftheendingperiodbalancefortheclosedyearmatchesthebeginningperiodbalanceforthenewyear,thereshouldbenoissue. -Victoria LikeLike Akram Reply January25,2011at11:38am caniattachthescreenshothereinthisforums… asitsfabrikam…iwouldlikeyoutoseethis…. LikeLike VictoriaYudin Reply January25,2011at11:46am Akram, Youwouldhavetopostthepicturessomewhereelseandthenputalinktothemhere.IwouldalsocautionyouaboutusingFabrikamforintensivetesting–ithasdatathatisnotquitecleanandIhaveseenissuesinthepastwithusingFabrikamtotestyear-endclosingspecifically.IfyouareonlyseeinganissueinFabrikam,thenitmayberelatedtohowthedatawasimportedinthere.Itisusuallybettertotestwithacopyofyourowndata. -Victoria LikeLike Akram Reply January25,2011at11:57am Case1.(Notmatchingcase) Case2:SummaryMatchingonlywithHistoricalDetails Hereyougo….. sameisthecasewithmylivecompanytoo…. ineedtogivemyFinancedeptareasonwhythisishappeningandtheystillcancontinueasitwillnothampertheirwork. ithappensonlyforBBFnotforanyotherperiods1to12…. -Akram. LikeLike Akram Reply January25,2011at12:12pm HelloVictoria, Herearethecasesbothscreenshot… Hopeyoucanhelpmeoutinthis… http://www.megaupload.com/?d=2EF0W9FI LikeLike VictoriaYudin Reply January25,2011at12:22pm Akram, Iamseeingthisinsomecasesinourlivedataaswell.Again,thisisnotanissue.ItissimplyhowGPisdisplayingthis.Theonlythingthatmattersonthebeginningbalancelineistheperiodbalance.Ifthisisarealissuefortheaccountingdepartment,IwouldrecommendtalkingtoGPSupporttoseeiftheycanexplainhowthisisgettingpopulated. -Victoria LikeLike Akram Reply January25,2011at12:52pm thanksforlotforbearingwithme…. Appreciateyourresponses…. LikeLike Vic Reply January17,2011at2:14pm HiVictoria HopeIaminthecorrectforum. Accountingpostedandinvoicelastmonthas12/01/2011insteadof12/1/2010.Mycfowouldliketonowchangethepostingdatto01/01/2011.Canthatbedone? Thanks Vic LikeLike Vic Reply November22,2010at9:55am HiVictoria MyControllerneedstoseeallopenbalanceswithatermofCC(CreditCardforus) Willanyofyourviewsshowmethat? Thanks Vic LikeLike VictoriaYudin Reply November22,2010at10:18am HiVic, Fromthedescription,itsoundslikeyouwouldwanttostartwiththeReceivablesUnappliedTransactionsview.Youwouldneedtoaddafilterontheterms. -Victoria LikeLike Vic Reply November22,2010at10:41am AsusualthePERFECTsolutionandaveryquickresponse!!!!! Thanksagain!!!! YouRock!! LikeLike Jim Reply November19,2010at12:31pm HiVictoria, Iwanttoseeifyoucanpleasegivemeyourinput.IneedtopostfewtransactionsinJune,JulyAugandSepthowever,weclosedtheperiodandallthestatementshasbeendone.CanIstillposttrxtoJune?Ifsowhatarethebestwaytoopenthepriodandpostthetransactionsandclosethemagain? Anythingelseneedstobedoneafterpostingthetransaction?likeReconciletoGL? Thankyou, LikeLike FAC Reply November19,2010at2:07pm maybeicanhelpwiththis didyouclosetheperiodof2010inConfig/Company/FiscalPeriodsbycheckingthemonthuwantedtoclose? Ifso,ifyouwanttoopenthatperiodujustuncheckthatmonthandposturtrxandcloseitagain noreconcileneeded theonlyproblemwouldbeifualreadyprintedlegalreportsfromthoseclosedperiods,datawillchangeanduwillhavelegalproblems(wellinmycountrywewould) ihopethatswhatuwanted LikeLike Jim Reply November19,2010at2:22pm FAC, Yes.Thatanswersmyquestion… Thankyouverymuch…. -Jim LikeLike Vigneshwaran Reply October10,2010at10:16am Hi, IhaveencounteredtheMDAissuewhileposting. IhaveverifiedGL10001andDTA10300tables.IfoundinthereisonelineinGL1001tableOrgseqnumfielddidnotmatchwithDTA10300Seqnumbrfiled. HowtheGL10001Orgseqnumgetupdated..?anylogicorsetuptablebehindthat..? IrequestyoutogivesomemoreinformationonMDAanalysisandGL10001Orgseqnumdetails.. LikeLike VictoriaYudin Reply October10,2010at11:02am Vigneshwaran, Sincethissoundslikeapossibleissuewithyourdata,IwouldrecommendcontactingGPSupporttomakesureyougetthemostcompleteinformationandsupportspecifictoyoursituation. -Victoria LikeLike Freedom Reply September7,2010at4:39am HiVictoria, SorryIforgottomentiontheimpactedtables. Thisaretheimpactedtables: SY00500 LikeLike Freedom Reply September7,2010at4:27am Thissiteisreallyhelpful.IwouldliketoasksomeconcernsonGP7.5: The1stproblemisthis,oneofareusersisabouttopostaIVADJ-Bbatchhowever,thebatchwasautomaticallymarkedtopostingpriortotriggeringtheactualPOSTbutton.Canthisbepossible? Next,werantheaudittrailtocheckandfoundoutthattherewasaIVADJ-AbatchranthatwascompletedandfinishedsuccessfullywhichimmedeatlyfollowedthepostingofIVADJ-B.Wedonotknowhowthishappenedconsideringthattheend-userdidnottriggerthePOSTbutton.WealsocheckedthattheIVADJ-Bwashas“marked”(MKDTOPOST)status‘1’andbatchstatus(BCHSTTUS)‘3’. Couldyouhelpusandexplainwhathappenedtothis?Thankyou LikeLike VictoriaYudin Reply September7,2010at8:04am Hello, Asyouprobablyknow,GP7.5hadnotbeensupportedforalongtime(moredetailhere),andIbelievethattherehavebeenimprovementsinbatchpostingerrorhandlinginnewerversionofGP.It’salsobeenaprettylongtimesinceIhaveworkedonGP7.5,however,mostoftheconceptsshouldbethesame. BCHSTTUS=3meansthatthebatchisstillreceivingtransactions.Whenthebatchisreceivingtransactions,thesystemwillnotallowanyoneelsetopostit,sothatmaybewhythemarkedtopoststatusis1.Ihaveseenthishappenwhenthereisaproblem,interruptionorerrorduringpostingandthesystemwillneedmanualinterventiontobeabletocompleteposting.TakealookattheBatchRecoveryandMasterPostingwindowstoseeifyoucanstillseetheoriginalbatchesandifthereareanymessagesorstatusesassociatedwiththemthere. -Victoria LikeLike Freedom Reply September7,2010at8:55pm HelloVictoria, Thanksforyourhelp.Wewereabletocheckedyourresolutionandfullyunderstandhowthisscenariobehave. Cheers, Freedom LikeLike KristieMcNulty Reply September1,2010at3:07pm Iamtryingtofindoutifanyonehaseverexperiencedlosingtheirdetailjournalentrylinesfromwithinabatch.ThebatchID,numberoftransactions,aswellasthedollaramountoftheentirebatchremainsintact,butthedetailswithinthatbatchhavedisappeared.WhenIchecktheGL10000table,allisasitshouldbe,butwhenqueryingforthedetailswithinthebatchintheGL10001table,thedistributionlinesarenotthere.IranFinancialChecklinksinhopesitwouldresynceverything,butnoluck.Ourbiggestconcerniswiththeintercompanyjournalentrieswhichoriginatedfromothercompanies,thedetailsarelostinsomeofthosebatchesaswellasothers.Myapologiesifthisisnottherightwaytogetthisissueposted,butIcouldnotfindwheretogotocreateanewpost.Thankyouinadvanceforanyhelp. LikeLike VictoriaYudin Reply September1,2010at5:26pm Kristie, Soundslikesomethingwentreallywrongsomewhere…Ihavenotencounteredthisbefore.TheonlythingIcanthinkofistryingtorestorefromabackupifyouhavetheabilitytogobacktowhenthedatamayhavebeenthere. -Victoria LikeLike KristieMcNulty Reply September16,2010at2:47pm ThanksVictoria–fromwhatwecouldtelltherewassomesortofhiccupwiththisparticulardatabaseandatthesametimethehourlytransactionlogwasnotgettinggenerated,thedetailswithinthejournalentriesweremysteriouslydisappearing.Wewereabletorecoverbacktothelasthourlytransactionlogontoatestdatabaseandreplicatethedetailsovertoourproductiondatabase,butwestilllostabouttwohourswithofjournalsbetweenthelastgoodhourlylogandthenextone.So,mylessonlearned,foryourlargerdatabases,monitorhourlylogs,andifpossible,createanalertifthere’sagaplargerthan1.25hoursbetweenlogs.Ouroutsourcingpartnerthinksthismayhaveallhappenedbecauseoflackofspaceonthehardwarewherebackupsarestoreduntilthey’removedtotape.JustsharingwhatIcaninhopesotherscanavoidthefiascowewentthroughtorecoverwhatwecould. LikeLike VictoriaYudin Reply September16,2010at5:35pm Kristie, Thankyouverymuchforthefollowupandsharingwhathappenedandwhatyouhavefound. -Victoria LikeLike BobMorrill Reply August31,2010at11:42am Hi, IamwonderingifthereanactualfieldinDynamicsthatholdsacumulativeGLaccountbalanceforeachaccountbalanceasopposedtoaddingperiodbalancestogether? Thanks LikeLike VictoriaYudin Reply August31,2010at3:12pm HiBob, IamnotawareofanytablesthatholdperiodbalancesforGLaccounts,onlynetchanges.Youwouldneedtoperformyourowncalculationstogetperiodbalances. -Victoria LikeLike BobMorrill Reply August31,2010at3:51pm Ok.Thanksforthehelp.Haveaniceday. LikeLike FAC Reply June21,2010at12:06pm HiVictoria Iwaswonderingthepostdateandtimeofatransaction,inORPSTDDTonlystoresthedatebutnotime thenithoughttheDEX_ROW_TSfieldinGL20000wasthepostanddatetime,butthatfieldshowedmetodaysdateaboutanhourago,imjustcuriouswhatdatastoresinDEX_ROW_TSandwhythatfieldhastoday’sdate,originalpostdatewasmonthsago. Thanksalotandpardonmyenglish youarethebest LikeLike VictoriaYudin Reply June22,2010at8:50am HiFAC, Tomyknowledge,theORPSTDDTisthe‘OriginatingPostingDate’,not‘original’.Slightdifference,butitisimportant,asinGP‘originating’meanswheresomethingcamefrom.Sointhiscase,theOriginatingPostingDateisthedatethetransactionwasoriginallypostedinthesubledgeritcamefrom.Forexample,ifyouarelookingataGLtransactionthatwascreatedbyaPayablesInvoice,thisdatewillshowwhenthePayablesInvoicewasposted.All‘old’datefieldsinGPwillonlyhaveadate,notime,eventhoughinSQLtheyareadatetimefield.DEX_ROW_TSisanewfieldintroducedinGP10.0andinthiscasewillholdthedateandtimetherecordwascreatedintheGL20000table,sowhenitwasactuallypostedintheGeneralLedger.Dependingonyourpostingsettingsandmanyotherfactors,thisdatecouldbedifferentfromtheOriginatingPostingDate.Also,thetimeintheDEX_ROW_TSfieldisshowinUTC(CoordinatedUniversalTime),notthetimeonyourSQLServer. Hopethathelps. -Victoria LikeLike sreekumarg Reply April3,2010at12:54am HiVictoria IsthereanythreadinthisblogthatdescribesaboutAnalyticalAccountingtables,structureetc..,asavailableforRM/PM/GL/CMetc.., Rgds..Sreekumar LikeLike VictoriaYudin Reply April3,2010at9:16am HiSreekumar, IdonotworkwithAnalyticalAccounting,soIdon’thaveanyresourcesforthattoshare. -Victoria LikeLike AllenMcEuin Reply March24,2010at11:34am AuserhascontactedmeafterrunningtheDistributionHistoryDetail–PayablesManagmentreportthatanaccountcameup“notfound.”IhavedeterminedthatinPM30200thefieldforCHEKBKIDisblankthoughthatdoesnotseemtobeconsistantfilledorblank.Anysuggestiononwheretostarttracingthisbacktothemissingaccount? Thanks, AllenMcEuin LikeLike VictoriaYudin Reply March25,2010at3:56am Allen, Typicallyanaccountnotfoundwouldmakemethinkthatthereissomethingwrongwiththeaccountindexonthetransaction.IwouldfindthetransactionthiscameupforinthePM30600andlookatalltheaccountindexesforit,thenlookthoseaccountindexesupinGL00100–aretheyallthere? -Victoria LikeLike Bill Reply March23,2010at1:22pm Victoria, IamattemptingtolinktheGL20000tabletoinventoryitemsfromreceiptsorpo’sandamhavingdifficultybecausethistabledoesnotseemtoholdtheoriginalpoorreceiptlinenumber.Anysuggestions? LikeLike VictoriaYudin Reply March23,2010at1:56pm Bill, Typically(anddefinitelyinthiscase)GPpostsinaggregate–ifyouhave5linesonareceiptthatallgotothesameaccount,youwillnotsee5separateGLdistributionsforthesameaccountinthesametransaction,youwillseeonelinecombiningthem.SothereisnothingtolinkaGLentryorpartofittoaparticularlinenumberonareceipt.PO’sdon’tgetpostedtotheGL,soyou’donlybeabletolinktoareceiptandthenfromtherebacktoaPO. -Victoria LikeLike marcia Reply March22,2010at9:40am DearVictoria, First,thanksforthisspace. Iamworkingwithanaliticalaccounting,andIneedtojointheGLwithAAGtables,butihavebeentroubleswithitbecauseiwasdoingitusingsqnclineandorigsecnumthatworksinthemostofthecasesbutitdoesnotforafewones. So,howcanihavesomethinglike select* fromGL00100 whereACTINDXnotin (selectACTINDX fromGL20000 whereTRXDATEbetween DATEADD(yy,-1,GETDATE())andGETDATE() union selectACTINDX fromGL30000 whereTRXDATEbetween DATEADD(yy,-1,GETDATE())andGETDATE()) -Victoria butwithaagtables. Hopeyoucanhelpme,thankyou. LikeLike VictoriaYudin Reply March22,2010at4:17pm HiMarcia, Iamsorry,IdonotworkwithAnalyticalAccounting,soIamnotagoodresourceforthis.IwouldrecommendpostingyourquestionononeoftheGPnewsgroupstoseeifanyonetherecanhelpyou. -Victoria LikeLike jubith Reply March3,2010at2:42am HiVictoria Thetableexplanationsforallofthemodulesaregreat.Veryusefulones. IamtryingifIcandeleteormarkinactivealltheGLaccountswhichhasnotbeenusedforlastoneyear.Itwouldsohelpfulformeifyoucansuggestanyqueryfortheprocess. thanks Jubith LikeLike VictoriaYudin Reply March3,2010at3:17pm Jubith, Iwouldnotrecommenddeletingaccountsfromthedatabase,asthatcanbreakallsortsofthingsinGP.Evenchangingthemtoinactivemightnotbethebestidea,sodefinitelytestthisthoroughlyandmakebackups.However,ifyou’resimplylookingtocomeupwithalistoftheseaccounts,hereisaquerythatwouldhelp: select* fromGL00100 whereACTINDXnotin (selectACTINDX fromGL20000 whereTRXDATEbetween DATEADD(yy,-1,GETDATE())andGETDATE() union selectACTINDX fromGL30000 whereTRXDATEbetween DATEADD(yy,-1,GETDATE())andGETDATE()) -Victoria LikeLike jubith Reply March8,2010at3:57pm Thanksforthereplyandquery.IwouldalsonotoptfordeletingtherecordsfromGP,ratherIwouldtickouttheoption‘AllowAccountEntry’. IfIfilterthesubquerywithDISTINCTACTINDX,itrunsfaster. thanks. *Jubith LikeLike Elisabeth Reply February1,2010at5:02pm HiVictoria, ThankyouforallofyouradviceandhelpfortheDynamicsGPCommunity,trulyappreciateit! Iwaswonderingifyoucouldassistmeorshedsomelightonanissuewehave.WejustupgradedtoGP10.0toGP9.0.Theupgradewentfine,andduringthetestupgradewepostedsometestinvoicestoseeifeverythingwasgoingalright. However,beingnewtoGPmyself,IdidamistakethatIonlyremovedthesepostedinvoicesfromtableRM20101…withoutrealizingthatthetestinvoicesputnumbersintotheGLandInvoiceRegistries. IsthereawaytocompletelydeletetheserecordsfromourGPtables?IseethatIcoulddeletetheGLfromGL20000table;howeveristhisgoingtoaffectanythingelse,asintheGLaccountbalancesinGL10100oretc? Thanksinadvancedforyourhelp!Greatlyappreciateit. -Elizabeth LikeLike VictoriaYudin Reply February1,2010at5:09pm HiElisabeth, UnfortunatelyyoudidsomethingthatIwouldnotrecommenddoing.😦Takingpostedtransactionsoutofatableisasubledgerisgoingtocauseallsortsofhavoc.Dependingonthedetailsofthosetransactions,therecouldbedozensoftablesthatgotupdated.Someofthemaresummarytableswhichaddtototalseverytimeatransactiongetsposted,sothereisnoeasywaytocleanlyremoveselectedtransactionsfromthem.Ifit’spossible,IwouldrecommendputtingbacktherowsyouhavedeletedorrestoringfromabackupandthencleaningtheseupfromtheGPuserinterfacebyeithervoidingthemorenteringreturns/creditmemos. -Victoria LikeLike Elisabeth Reply February1,2010at6:13pm Victoria,thanksforthequickanswer. IagreewithyouandIthinkwe’regoingtoputbacktherowsIdeletedandlet’sseeifwecouldjustvoidtheseinvoices.I’llletyouknowhowitgoes. Thanksforyouradvice! LikeLike FAC Reply January26,2010at8:09pm HiVictoria thanksforallyoursofastrepliesyouareveryhelpfullandthebest. Welliamgoingtobotheryouwithanotherproblem(sorry) Someonescrewupthepostconfigurationandtodayallthepostingfromeverymodulewerenotgeneratingjournalentries(causethe“postdirectlytoGL”wasnotchecked) wellafterikilledthatguy😉iamtryingtofixthishugemistake, restorethedatabasetotheyesterday’sbackupisnotanoptioncausetherearealotoftransactionsanduserswillnotdoallthetransactionsagain, soiwasthinkingtomakeaqueryofallPM,RM….postedtransactionsthatarenotpostedinGL20000(wellalltransactionsoftoday) somethinglikethis(ie.PM20000table) SELECTVCHRNMBRfromPM20000WHEREVCHRNMBRNOTIN(SELECTORCTRNUMFROMGL20000) thenigetthedistributionsfromPM10100ofthatdocumentsandthenmakeajournalentryforeveryVCHRNMBRanduploadittoGP(throughIntegrationManager)andthenpostthem. isthiscorrect?orthereisanotherwaytodothat? thanksalotVictoria LikeLike VictoriaYudin Reply January27,2010at4:33am FAC, Yes,yourapproachsoundslikeitwillwork.YoumaywanttoenlistthehelpofGPSupportoryourpartnertoensurethatalltransactionsarecaptured.Theremaybequitealotoftablesandinformationtowadethrough. Also,sinceitmaybeverydifficultto‘link’theGLtransactionstothesubledgersafterthefact,anotheroptionmaybetogatherthetotalsofthedistributionsandcreateaGeneralLedgertransactionwithjustthetotals. Finally,takeaccesstothePostingSetupwindowawayfromallusers. -Victoria LikeLike Jim Reply January21,2010at2:55pm Victoria, IamtryingtofindthedetailsfromGL.IhaveJrnlNo. andAuditcode(StartswihtGLTrx….)HowdoIgetthedetailinfo?Canyoupleaseseeifyoucanhelpmeout? -Jim LikeLike VictoriaYudin Reply January21,2010at3:52pm HiJim, Whatkindofdetailsareyoutryingtoget? -Victoria LikeLike SanjayParab Reply January13,2010at11:06am HelloVictoria, IneedyourhelpinabovelistedCommonlyUsedTablesGLTabelsforGLReports. WIllyoupleaseletmeknowaboutTablesseriesofDTA.whatdatahasbeenstoredinDTAtables?WhatsstandsforMDAorpleaseletmeknowwhatisMDATransactions? LikeLike Gordon Reply December29,2009at6:38pm AnysuggestionsonhowtogetstartedonaCrystalreportthatshowsmeactualvs.budget,currentmonthandyeartodate.IuseFRxforthisforfinancials,butthereisaneedforexternallocationstotracktheirspendingandIthoughtCrystalonCitrixwouldbetheonlywaytogoformycashstrappedcompany.Thiswaymanagerscouldlogin,runtheCrystalreportandseeexactlywheretheyarewiththeirspending…andnotbothermefortheinfo!🙂 Thanksandhappyholidaysall! Gordon LikeLike VictoriaYudin Reply December30,2009at8:41am Gordon, Ifyou’reonlylookingtoreportonP&Laccounts,thisshouldbefairlystraightforward.Iwouldstartwiththefollowingviews/tables: GL11110–OpenYearSummaryMaster GL01201–BudgetSummaryMasterView(orGL00201–BudgetMasterformultiyearbudgets) -Victoria LikeLike Gordon Reply December30,2009at2:52pm Victoria, Thatworkedoutreallywell,thanks.Iamonmyway. Basically,IhavethereportgroupedbyGLaccountandinthedetailsaretheactualsandbudgetamountsforeachperiod.However,forsomereason,iftheactualforagivenperiodis0.00,butthereisabudgetamount,itwillnotdisplaytherecord.Thisisproblematicbecauseitisnotcalculatingthecorrecttotalforeachaccount.Ineedittodisplayperiodbalancesthatare0.00. Anythoughtsonwhythisishappeningormoreimportantly,howtocorrectit? Thanksandregards, Gordon LikeLike VictoriaYudin Reply December30,2009at3:11pm Gordon, YouprobablyneedtohavetheGLaccountmaster(GL00100)asyourfirsttable,sothatyoucapturealltheaccounts,thenlinkinGL11110andGL01201. -Victoria LikeLike Gordon Reply December30,2009at3:32pm OK,thattableisinthemix.IallowedittoAuto-Linkthetablessincemylinkingisweak.Inlookingoverthelinksitfeelscompelledtolinkall4tables(IaddedGL00105fortheACTNUMSTfield)byeachoneoftheACTNUMBR_1,…2,…3,andsoontoACTNUMBR_10. Isthatoverkill?ShouldItrytodothelinkingmyself? Thanks, Gordon LikeLike VictoriaYudin Reply December30,2009at3:37pm Thatisabsolutelyoverkill.TheonlylinkingyouneedisontheACTINDXwhichwillbepresentineveryoneofthosetablesandthenpossiblyontheperiodID’s,dependingonhowyouarecodingthatinyourreport.Useleftouterjoinsonthetabletobesafe. -Victoria LikeLike Gordon Reply December30,2009at4:38pm OK,thatdidn’tpickupthemissingamounts. Iamactuallytryingtogetthisdonerightwithonecompany(asIknowyouhaveadvocatedinapreviousemailfromafewmonthsback),butnotinaviewbutjustwiththedatabaseitself. So,nowIamgoingtotrytocreatetheview(onecompanyonly)toseeifthatwillgetmetowhereIwanttobe. Insettingitup,Idon’tseethatIevenneedGL00100asIcangettheACTDESCRfromGL11110,soIremovedthattable.WhenIrunthequery,theresultsarenotwhatIwant,somylinkingmustbewrong. UsingGL11110andGL01201,howcanIgettheresultstogivemetheperiodbalanceandbudgetamountjustonceforeachaccountforeachperiod? Thanks. Gordon LikeLike VictoriaYudin Reply December31,2009at7:33am Gordon, Therearemanydifferentwaystodothis.Hereisoneoption: selectn.actnumst[Account], a.actdescr[Name], gl.periodidPeriod, coalesce(gl.perdblnc,0)Actual, coalesce(b.budgetamt,0)Budget fromgl00105n innerjoingl00100a onn.actindx=a.actindx leftouterjoingl11110gl ongl.actindx=n.actindx andgl.year1=2009 leftouterjoingl01201b onb.actindx=n.actindx andb.budgetid='2009'--changetoyours andb.periodid=gl.periodid -Victoria LikeLike VictoriaYudin Reply December31,2009at7:47am Gordon,actually,thismightbebetter: SELECTn.ACTNUMSTAccount, d.ACTDESCRName, a.PERIODIDPeriod, SUM(CASEa.AmtType WHEN'Actual' THENAmtELSE0 END)Actual_Amt, SUM(CASEa.AmtType WHEN'Budget' THENAmtELSE0 END)Budget_Amt FROM (SELECTACTINDX,PERIODID, BUDGETAMTAmt,AmtType='Budget' FROMGL01201 WHEREBUDGETID='2009'--change UNIONALL SELECTACTINDX,PERIODID, PERDBLNCAmt,AmtType='Actual' FROMGL11110 WHEREYEAR1=2009--change )a LEFTOUTERJOINGL00100d ONa.ACTINDX=d.ACTINDX LEFTOUTERJOINGL00105n ONa.ACTINDX=n.ACTINDX GROUPBYn.ACTNUMST, d.ACTDESCR,a.PERIODID -Victoria LikeLike Gordon January4,2010at12:35pm ThanksVictoria,IwillgivethisatryandletyouknowhowImakeout. HappyNewYear! Gordon LikeLike Gordon January5,2010at6:31pm Victoria, Preliminaryresultsareinandsofarthingsarelookinggood,reallygood. I’llkeepyouposted. Thankssomuch. Regards, Gordon LikeLike Gordon January6,2010at11:26am Victoria, I’vesuccessfullycreatedthisreportforonecompanyandhavenowmadeitsothatitreportsdatafrommultiplecompanies. However,withonecompany,itwaseasytojustusetheSelectExperttogiveitarangeofGLaccounts(theIncomeStatementaccounts),butwithmultiplecompanies,thatrangeintheSelectExpertwon’tworkforall. WouldIhavetoentertheaccountnumberrangeintheSQLvieworcouldIsomehowsetuptheSelectExpertatthereportleveltogivemeonlytheIncomeStatementaccounts? Thanksandregards, Gordon LikeLike VictoriaYudin January6,2010at12:24pm Gordon, My2cents:ifyou’realwaysonlygoingtowanttheIncomeStatementaccounts(orP&LaccountsasGPcallsthem),thendothisinSQL–ifyouhavealotofdatathiswillspeedupprocessing.YoucouldlimityourselectiononGL00100.PSTNGTYP=1.Ontheotherhand,ifyouthinkyoumightneedtoseealltheaccountsinafutureversionofthisreport,thenbringintheGL00100.PSTNGTYPcolumnsothatyoucanusethatintherecordselectionformulainCrystal. -Victoria LikeLike Gordon January7,2010at12:21pm Victoria, Thatdidit!Thanks.IthinkIhaveeverythingIneedonthisreport. Thankssomuchforyourassistance. Regards, Gordon LikeLike SanjayParab Reply January13,2010at10:48am HelloVictoria, TonsofCongratsforMVP..IneedyourhelpinabovelistedCommonlyUsedTablesGLTabelsforGLReports. WIllyoupleaseletmeknowaboutTablesseriesofDTA.whatdatahasbeenstoredinDTAtables?WhatsstandsforMDAorpleaseletmeknowwhatisMDATransactions? LikeLike VictoriaYudin Reply January14,2010at4:16am Sanjay, MDAstandsforMultidimensionalAnalysis,aGPmodulethatallowstrackingofadditionalinformationforGLaccounts.SimilartoAnalyticalAccounting,butmuchsimpler.IfyourcompanyisnotusingMDAthereisnoreasonforyoutoeverneedtheDTAtablesforyourreporting. -Victoria LikeLike Larry Reply December29,2009at11:36am Howcanifindoutwhattablesthesmartlistunderfinancial->accounttransactionsuses LikeLike VictoriaYudin Reply December30,2009at8:33am Larry, ThereisamanualcalledIG.pdfondisk2oftheGP9.0installationthatliststhetablesintheoutoftheboxSmartLists.ForAccountTransactions,hereisthelist: GL10001–GL_TRX_LINE_WORK GL20000–GL_YTD_TRX_OPEN GL30000–GL_Account_TRX_HIST MC40200–MC_Currency_SETP GL00100–GL_Account_MSTR GL10000–GL_TRX_HDR_WORK -Victoria LikeLike Erik Reply March9,2010at4:13am Larry, There’saViewcalled‘AccountTransactions’. Youcancheckit. Erik LikeLike Mellisa Reply November30,2009at10:55am HiVictoria, IhaveaclientthatislookingtofindthetablenameusedafteraninventoryrollandbeforeRevalsohecancreateaCrystalReport..Canyoutellmethenameofthetablehecanuse? Thanks, Mellisa LikeLike VictoriaYudin Reply November30,2009at11:06am HiMellisa, Mostlikelythereismorethanonetableinvolved.Iamnotquitesurewhatyoumeanby‘inventoryroll’,butingeneralIthinkyourclientmayneedtogiveyousomemoredetailedspecificationsforthereporttheyarelookingtocreateforyoutohelpthemfindtheappropriatetables.Sorrynottogaveaquickanswer. -Victoria LikeLike JohnChastain Reply December12,2009at3:06pm Ifyouarereferringtohavingmanufacturing,thenthetableyouprobablywantisICIV0323. LikeLike Joe Reply August26,2009at12:42am Thanksforyoursummaryoftables.Itisveryhelpful.IwaswonderingifyoucantouchonaddingcustomtablestoGP.InGP9,thereareonly4userdefinedfields.BecauseGLaccountshavemultipledimensions,itwouldbegreatifonecouldaddanadditionaltableforuserdefinedfields. Itwouldbeevenbetterifyoucouldupdatethetablesintheaccountmaintenancewindow. Thanksforyourthoughts, Joe LikeLike VictoriaYudin Reply August26,2009at4:40am Joe, Canyouelaboratealittleonwhatyou’relookingtotrackintheseadditionaldimensions?Therearesometoolsavailablethatmighthelp,likeExtender,MDAandAA,butwhetheranyofthemcanbeusefulinyoursituationwoulddependonthespecificneed. -Victoria LikeLike Atif Reply July27,2009at11:31am Iwouldliketohaveaqueryforrecievablesmanagementtogetthefollowinginformation– Invoicenumber GLPostDate CounterpartyName AmountDue CashDueDate DatePaid AmountPaid DescriptionField DaysPastDue Canyouprovidemethequery? LikeLike VictoriaYudin Reply July27,2009at11:38am Atif, Idonothaveaquerylikethisavailableanditwouldtakesomeworktoputtogether.Ifyouareinterestedinhavingthiscreatedforyouasabillableproject,pleaseletmeknow. -Victoria LikeLike Atif Reply July27,2009at5:08pm Victoria, WouldyoubeabletotellmewhattableswillIbelookingfortobuildthequery? LikeLike VictoriaYudin Reply July28,2009at6:44am Atif, IwouldstartwithRM20101,RM30101,RM20201andRM30201.MyRMTablespagehasmoredetailonthese. -Victoria LikeLike BrentKeilin Reply July16,2009at1:29pm HiVictoria, Thetableexplanationsforallofthemodulesaregreat…Ireferenceitallthetime. Iwaswonderingifyoucouldassistmeorshedsomelightonanissuewehave.Somehow,aPOwascreatedandseveraloftheitemsweremarkedas“Non-Inventory”eventhoughttheyareInventoryItems.ThePOwasreceivedandposted.Now,ourGeneralLedgerhasatotalforthisPOthatdoesnotmatchtheSubLedgerbecausethesub-ledgeronlyincludedtheInventoryItemsfromthePO.Anythoughtsorsuggestionsonhowtocorrect/proceed? Thankssomuch. Brent LikeLike VictoriaYudin Reply July17,2009at10:16am HiBrent, IftheGLiscorrectandtheseitemsarestillinyourphysicalinventory,thenIwouldadjusttheinventorysubledgerbyenteringaninventoryincreaseadjustmenttransactionandnotpostingittotheGL.Hopethathelps. -Victoria LikeLike JohannesHenn Reply June9,2009at10:11am Victoria Thankyoufortheadvice.IhadanotionthatitwouldusetheGeneralledger.HavingneverusedMGPbeforeIfindmyselfintheproverbialdeepend.IhavesentanemailtotheuserstodeterminehowtheyuseMGPfortheirassetmanagement. IamfromtheothersideoftheworldinCapeTownSouthAfrica.IamabusinessanalystdoingdataanalystworkonanEDWproject.TheclientisinCentralAfrica. Justtomakemyjobalittlemoredifficult,theDBIhaveisthestructureonly,nodata… Johannes LikeLike JohannesHenn Reply June9,2009at9:38am Victoria Icertainlygainedsomeinsightfromyourwebsite.Ihaveaverydifficulttaskathand.IhavetodoSourcetoStageandSourcetoModelmappingfromtheMGPdatabase.WhatmakesitdifficultisthefactthatIdonothaveanMGPsystemtoworkwith.Ihavetomapstraightfromthetablesandcolumnheaders. Ivirtuallyhavetofindoutwhattablesareutilizedforaspecificreportandthenworkoutwhatfieldreferstoaspecificreportmeasure. Thereforyourrefrencestothetablenamesandwheretheyfitin,ishelpingme.Iamhoweverstuckwithassets.ThedatabasethatIhave,doesnothavetheFA*****tables.Whatothertablescanbeusedforassets? Regards Johannes LikeLike VictoriaYudin Reply June9,2009at9:51am Johannes, IfthedatabaseyouhavedoesnothaveFAtables,theFixedAssetsmoduleisnotbeingused,soassetsarebeingtrackedsomeotherway.Youwouldneedtotalktotheuserstofindouthowassetsarebeingtrackedandwhere.ThetwomostcommonmethodsIhaveseenwhennotusingtheFixedAssetsmoduleare:(1)settingupaGeneralLedgeraccountforeachassetor(2)trackingassetdetailsoutsideofGP,typicallyinExcel,andonlyenteringsummarytransactionsintoGP’sGeneralLedger. -Victoria LikeLike Will Reply April23,2009at12:32pm Victoria, Thanksagainforthishelpfulinformation.I’mcurrentlyusingSBFandIdonothaveaGL11110orGL11111tables.(Wewillbeupdatinglaterthisyear!)DoyouknowofawaytolinkaccountdescriptionstotheGLtables?Forexample,IhavetriedmanywaystolinkGL00100toGL20000orGL30000withnosuccess. Iamattemptingtopulltheexpensesbrokenoutbycategory,insteadofjustCGS.Doyouknowofawaytodothis?Thanks! –Will LikeLike VictoriaYudin Reply April23,2009at12:35pm Will, YoucanlinkGL20000orGL30000toGL00100onACTINDX.YoumayalsowanttolinkinGL00105(alsoonACTINDX)forthefullaccountnumber. -Victoria LikeLike Joe Reply February3,2009at3:27pm Victoria, IjustdiscoveredyoursitetodaywhilewritingsomecustomreportqueriesforDynamicsGP.Thisinformationisveryhelpful. Ihaveabackgroundindatabasedesign,SQL,Java,Linux,sys-adminandWindows-LinuxinteropbutIdon’tknowmuchabouttheinternalsofDynamics.Withrecentdevelopmentshereatwork,I’mnowforcedtolearnaboutDynamics.Sofar,it’sbeeninterestingandyouhaveshortenedthelearningcurvesomewhatwiththeinformationyouprovideonyourblog. Cheers, Joe LikeLike TauqirAbbas Reply December6,2008at6:10pm thankx LikeLike LeaveaReplyCancelreply Enteryourcommenthere... Fillinyourdetailsbeloworclickanicontologin: Email(required)(Addressnevermadepublic) Name(required) Website YouarecommentingusingyourWordPress.comaccount. ( Log Out /  Change ) YouarecommentingusingyourTwitteraccount. ( Log Out /  Change ) YouarecommentingusingyourFacebookaccount. ( Log Out /  Change ) Cancel Connectingto%s Notifymeofnewcommentsviaemail.Notifymeofnewpostsviaemail. Δ Searchthisblog: Searchfor: MyLastBookDynamicsGP2013ImplementationMyPreviousBookDynamicsGP2010ImplementationEmailSubscription Enteryouremailaddresstosubscribetothisblogandreceivenotificationsofnewpostsbyemail. EmailAddress: Signmeup! Join3,127otherfollowers GPReportsViewerNewsletter SignupfortheGPReportsViewerNewslettertogetreportingtipsviaemail RSSFeeds RSS-Posts RSS-Comments Tags PopularLatestComments articles awards BankReconciliation book CrystalReports DynamicsGP Excelreports Extender featured FixedAssets FRx GeneralLedger GP8.0 GP9.0 GP10.0 GP2010 GP2013 GP2015 GPReportscode GPReportsViewer GPSQLstoredprocedure GPSQLview GPtableinformation IntegrationManager Inventory ManagementReporter Microsoft MicrosoftMVP Miscellaneous MSDynamicsWorld Payables PurchaseOrderProcessing Receivables SalesOrderProcessing security servicepack SmartListBuilder SQLcode SQLServer SupportDebuggingTool supportlifecycle version Vista Windows7 yearendclose UsingcreditcardstopayvendorsinDynamics GP January4,2009 SQLviewforPayablesGLdistributionsinDynamics GP November28,2008 SQLviewforallpostedGLtransactionsinDynamics GP August11,2009 SalesTransactionEntryGLdistributionsinDynamics GP December8,2008 SQLviewtoshowAPapplyinformationin GP October22,2008 SQLviewforPayablespaymentapplydetailin GP October22,2009 YearendcloseinDynamics GP November14,2008 SQLviewwithARapply detail February15,2010 GrantingaccesstoanewSmartListinDynamics GP October8,2008 SQLviewwithallSOPline itemsinDynamics GP May17,2009 SQLviewforunpostedchecksinDynamics GP August29,2017 SQLviewforrolling12monthsofsalesbyitembysiteinDynamics GP April20,2017 DynamicsGPsalesamountsandquantitiesforrolling12 months March14,2017 UpdatingDynamicsGPdatadirectlyin SQL January17,2017 DidyouknowyoucanchangeVoiddatesonPayables transactions? September20,2016 RefreshableExcelreportformonthlyactualsvs.budgetsinDynamics GP May25,2016 SQLcodetoshowtopXpercentofvendor data March21,2016 DiscountfromPackt Publishing September15,2015 RefreshableExcelreportforhistoricalGLtrialbalanceinDynamics GP August13,2015 RefreshableExcelreportformonthlyGLbudgetinDynamics GP April28,2015 VictoriaYudin:Unfortunately,no.:(... Mirandacoulson:ifweforgottochangethiswhenvoidingisthere... Kurt:ExactlywhatIwaslookingfor!Thankyouverymuc... VictoriaYudin:HiAlyssa, Pleasetrythis:https://victoriayudin... Alyssa:HiVictoria, Thankyouforallyourinformationy... VictoriaYudin:Scott, ThatshouldbeintheresultsasthePayme... Scott:Youareawesome!Thankssomuch.Imighthavemo... VictoriaYudin:HiScott, Thereare2tables:B3920200andB39200... Scott:Yes,youarecorrect!IsB3920200thetableInee... VictoriaYudin:HiScott, IbelievethatisfromBinaryStream's... Links DynamicsGPCommunity DynamicsKnowledgeBase FlexibleSolutions,Inc. GPReportsViewer GPWindow–searchforanythingGP MSDynamicsWorld Forums AsktheGPCommunity DynamicsUserGroup–GP MicrosoftPartnerForums TekTips–GP Blogroll AaronBerquist AmberBell BeatBucher ConstanceQuigley DavidMusgrave DevelopingforDynamicsGP DevonSouthall DwightSpecht DynamicsGPEngineeringTeam DynamicsGPLand DynamicsGPSupportandServicesBlog DynamicsCare eOneDynamicsGPandCRMBlog ERPSoftwareBlog FrankHamelly GloriaBraunschweig GSLBlog IanGrieve IanStewart JanHarrigan–FRxandMR JanakiramM.P. JenKuntz JimHummer JivteshSingh JohnLowther JonathanPCox LesWright LeslieVail MarianoGomez MarkPolino MichaelD.JohnsonII MicrosoftMVPBlog MicrosoftTeamBlogs MohammadR.Daoud MSDynamicsPartnerBlog OfficialDynamicsGPBlog RonWilson RoseBusinessSolutions ShawnDorward–GPLifeHacks SivakumarVenkataraman SteveEndow TimWappat VaidyMohan Pages GP2016Resources GPResources(builds 18.x) Resources GP2015Resources GP2018Resources DynamicsGPResources GP2013Resources GP2010Resources GP10.0Resources GP9.0&Older Versions SQLServerResources MR&FRx Resources GP Reports CrystalReportsTips GeneralLedgerSQL Code InventorySQLViews PayablesSQLViews SOPSQLViews SQLServerCoding Tips System/SetupSQLCode ReceivablesSQLCode GPTables BankRec Tables Company/System Tables FixedAssets Tables GL Tables Inventory Tables Payroll Tables PM Tables POP Tables RM Tables SOP Tables AboutMe FunStuff MostClickedLinksvictoriayudin.wordpress.c…myemail.constantcontact.c…victoriayudin.files.wordp…victoriayudin.files.wordp…mbs2.microsoft.com/fileex…victoriayudin.files.wordp…victoriayudin.wordpress.c…smpdl.sap-ag.de/~sapidp/0…cid-bc679914609aa946.offi…packtpub.com/microsoft-dy…RecentBlogComments VictoriaYudinonDidyouknowyoucanchangeVo…MirandacoulsononDidyouknowyoucanchangeVo…KurtonCodingspecificdatesinSQL…VictoriaYudinonGL TablesAlyssaonGL Tables CategoriesCategories SelectCategory CrystalReports  (17) DynamicsGP  (166)    Extender  (3)    GP10.0  (19)    GP2010  (22)    GP2013  (8)    GP2015  (3)    GP8.0  (9)    GP9.0  (11)    GPSQLscripts  (74)    SmartListBuilder  (5) Events  (6) FRx  (5) GPReportscode  (100)    BankRecSQLcode  (2)    CrystaltipsforGP  (11)    Excelreports  (4)    FixedAssetsSQLcode  (3)    GLSQLcode  (14)    InventorySQLcode  (9)    PayablesSQLcode  (20)    POPSQLcode  (2)    ReceivablesSQLcode  (12)    SOPSQLcode  (22)    SQLcoding  (8)    System/SetupSQLcode  (7) GPReportsViewer  (14) ManagementReporter  (2) Microsoft  (21) Miscellaneous  (14) SQLServer  (18) SSRS  (1) Uncategorized  (3) Hitssince9.17.2008 7,038,903 Archives Archives SelectMonth August2017 (1) April2017 (1) March2017 (1) January2017 (1) September2016 (1) May2016 (1) March2016 (1) September2015 (1) August2015 (1) April2015 (1) March2015 (1) January2015 (2) November2014 (2) September2014 (2) July2014 (2) April2014 (2) March2014 (1) January2014 (4) November2013 (1) October2013 (1) September2013 (1) July2013 (1) June2013 (2) May2013 (2) April2013 (1) March2013 (1) February2013 (1) January2013 (2) November2012 (1) October2012 (2) September2012 (1) August2012 (2) July2012 (1) June2012 (1) May2012 (3) March2012 (1) February2012 (2) January2012 (5) December2011 (2) November2011 (1) October2011 (3) September2011 (2) August2011 (2) July2011 (1) June2011 (1) May2011 (3) April2011 (1) March2011 (3) February2011 (5) January2011 (5) December2010 (2) November2010 (4) October2010 (2) September2010 (4) August2010 (3) July2010 (4) June2010 (3) May2010 (7) April2010 (2) March2010 (4) February2010 (3) January2010 (2) December2009 (1) November2009 (1) October2009 (3) September2009 (4) August2009 (3) July2009 (3) June2009 (3) May2009 (3) April2009 (3) March2009 (5) February2009 (4) January2009 (8) December2008 (6) November2008 (8) October2008 (21) September2008 (7) VictoriaYudin. BlogatWordPress.com. Follow Following VictoriaYudin Join3,127otherfollowers Signmeup AlreadyhaveaWordPress.comaccount?Loginnow. VictoriaYudin Customize Follow Following Signup Login Copyshortlink Reportthiscontent ViewpostinReader Managesubscriptions Collapsethisbar %dbloggerslikethis: