Method Community

Aloha Everyone,

I appologize in advance for posting this question, but I am new to method and to the forums.

We have developed a very custimzed application for use in our student enrichment center using MethodCRM which is mostly modified existing CRM screens where much of the CRm cabpabilities have been removed to simplify the use for the office staff.   The key is to maintain the integration with Quickbooks.

We are using customer to represent the family and contacts to represent the members (children, parents, grandparents, etc.) of the family.  All of the initial databased development has gone very well with a little bit of guidence from support.  Invoicing has been modified to include 5 custom fields from Quickbooks and invoicing is only allowed when referencing a child. (Mom and Dad can't buy anything.)   All of this is working just fine.

Our problem is now that we are ready to develop some specialized reports we don't see how.

In particular, we have a need for a registration report that uses information from the invoice line table.  A student is not registered until an invoice is created.  We are able to create the report but I need to retrieve the student's birthday and allergies from the contact file.  I don't see anyway to retrieve them for when I se the invoice line file as the master the customer contacts do not appear to be available.

Is there a solution that I am missing?  I feel like I need the Report Designer ffor Dummies class. 

Thanks in advance for any quidence.

Mahalo

Hello Stuart,
      There are videos on the Report Designer.  They are now located in our Help Centre and have been converted to Youtube videos.  You can find them here.

     I believe what you are looking for is not in the Report Designer.  It is actually a table relationship problem.  The InvoiceLine table is connected to the Invoice table.  Through this relationship, you can find the Customer/family associated with the invoice. (Hence, you can see all three table in the Field List in Report Designer) Once you have the customer, the problem is there is nothing there to say what contacts are for what invoice line.  ie. There are three kids in a family. Mary signs up for painting, Joe for pottery and Sam is not registered.  

    You have to look at the tables and see how they are linked.  I would suggest adding a relationship from the invoiceLine table to Contacts.  Filter the list to show only the contacts/kids from that invoices customer/family.  When entering the invoice lines, you can select the right child and then have a link to go get the Contact/kid information.  It gets confusing because you may have to deal with the Entity table.  It may be a drop down filtered by EntityID matching the customer EntityID/RecordID and only show entities that have a contact name.

   There was another customer on the forums here that built similar things.  I know I suggested a relationship table between contacts and courses.  You can modify the invoice button's actions to add a record to the table. Then you can create a screen for the contact - courses table and print out the list. You would have the relationship to the contact table there to print a full list of kids in the course and there birthday and allergies.

    I will see if I can look into this further and try a couple of things.  

- Greg

Greg,

Thanks for the response.  I have spent a good portion of today working with the Report Designer.  In doing so I have learned a good deal and actually created a screen that can do what I need the Report Designer to do. 

I created a screen that has a grid at the top using the Transaction Table as the basis.  It displays the various transactions.  I then filter down to those transactions that are detail lines and by the Transaction Type being equal to either  InvoiceLineItem  SalesReceiptLine or CreditMemoLine.  Using those three types of transactions I can find all the students registered for a class.  (We have custom screens for these three tasks (Invoice, Sales Receipt and Credit Memo) that loads a custom field called Student Name that comes from the contact table.  We did this as a custom field from Quickbooks because we want the information to be visable in both Quickbooks and Method.)  Using the Transaction.Txn.Detail.ReordId I can then actually retrieve the respective entry from the InvoiceLine, CreditMemoLine or SalesReceiptLine tables which actually includes the Student's name as noted. Using the Student Name we can then retrieve the appropriate information from the Contact file.

This works fine on the screen where we can select one transaction and then display the information from all three tables.  It works well as proof of concept, but now I would like to do the same thing if possible in the Report Designer.  I sure have not seen the right way to do it in the Report Designer as the links that come up don't match what we need to do.   Is there a way in Report Designer to do something similar to the Retrieve field from Table that is on the screens?  Might it be possible to do something like that with a script? 

Sorry for the confusion and appreciate and guidence.

Stuart

Hi Stuart,

I have been following this thread between you and Greg...... Forgive me if I am coming in the middle, but Report Designer does have its challenges and I have been working with it for a bit. 

It sounds like your setup is where Parent or Family is Customer and then Contact s(customfield for invoice) are for   childen.  .  Then for the report you want the cucstomer/contacts listed with the relevant items they registered for  - correct?

An alternative would be to  parents set up as Customers and then Children as "Jobs" of Parent.  Then invoiceing would be at the "Job" level... To get a report of parent activity, you can then "group" your report by "Parent" and see all invoices under that 'Parent'.... In QB the invoice would be to the Child but could have parent address, etc.    

But either way, the attributes like birthday, allegeries, etc. are still in your contacts table. you still have the RD problem of linking data for reporting, cause you want attribtues that are just not in your invoice/invoiceline table. 

What you did with the screen grid is great but unfortunately, report designer is not so helpful...  The Transactions Tble is a special QB table that brings together basic information about transactions from QB.  I don't think you can or should add fields to this table in Method. 

Sometimes when doing reports with the designer(RD), I have had  to add Fields to my table to include the data I want in my report.  I will add customer attributes that are in the entity table to an invoice or invoiceline table so that they are readily able for reporting.You can not just add a "linked" field to your table.. It has to be a real field in the table for RD to work.   Or to create a table just for reporting. which you populate by using actions.  For example, I like to use pivot grids in RD because data can be summarized and grouped easily.  But in RD, a pivot grid report can only come from one table. so I have to add  attributes I want to my table so that it is self contained. 

As Greg mentioned, to add these fields to your tables, you need to save and insert into the table when a new entry is made.  Or generate an update loop to populate the tables before reporting. 

Sounds like a fun project... I hope I gave you some ideas. Method is great for a scheduling project like yours. Feel free to contact me offline if you would like more assistance.

Aloha Fran,

Thanks for jumping in.  All the better.  I am relatively new to Method and fyi my background was 30 plus years designing and coding business applications for Digital Equipment's OpenVMS systems.  If I had known more about Method and the integration with Quickbooks I might have approached this slightly differently, but I have what I have and need to make the best of it.

I am trying to stay with CRM and not go to Pro if possible.  I can add fields but would need Pro to add new tables, which might ultimately be the best solution to the problem.

Overall I am very pleased with the flexibility that Method provides and the Sync with Quickbooks appears to work very well.  The Report Designer on the other hand is less than what I had hoped for.  I probably need more time understanding the relationships and how to create new relationships.  I am somewhat hesitant since the application is live and would hate to screw something up adding relationships.  The one requirement we have is to be able to maintain the integration with Quickbooks so I agree I don't think I want to add anything to the Transaction Table.

Turns out that class registrations are based on Invoicing, Sales Receipts and Credit Memos screens which have all been highly modified to use the Item as the class which along with some custom fields added to Quickbooks which allow us to either invoice, sales receipt and credit memo in Quickbooks or Method.  This works very well and I can even create a report in Quickbooks that will provide all the basic info for class registrations from the Transaction file looking only at the InvoiceLineItem, SalesReceiptLine and the CreditMemoLine transaction types.  I can also create a reasonable report from the Report Designer except that some specific information which is kept in the Contacts Table is needed. (Allergy, Age, etc.)

One thought I have had is to write some Script that might allow me to actually open the Contacts Table and retrieve specific fields.  Any thoughts on this approach.  If so do you know if anyone has done it and if maybe some of the script might be available?

Also do you know of any documentation that I might be able to read to get a better handle on the Report Designer?

Appreciate any input you might be able to provide.

Mahalo,

Stuart

Hi Stuart,

Happy New Year....

Method is an awesome tool and great for the type of project you are embarking upon.

You are on the right track using custom fields on your invoice lines.  this way QB can get the data and you can report on it...  But to bring in the othr attributes even from QB would require a custom reporting solution..  Which could be done extracting the data from the QB from the Entity table  and stored procedure Dtail Transaction Report and then matching in Excel and generating report there.   Of course, this defeats the purpose o using Method. 

Unfortunately, you can't do this with Method RD.   Within RD, you can not retreive data from tables that are not listed in the datatables. And its usually only two related tables.  

So this is why I sometimes will insert the data needed for the report, like to the invoiceline table, to get all of the data needed for the report in one table.   It can not be a linked field, it has to be a real one. Its seems backwards (and against database structure rules) but works. RD limits reports to two related tables, plus the company table.   this can be routine functionality when someone registers with appropiate actions.

With the Method basic subscription, you can add fields to existing tables. But you can not add new tables.  However, I would think that with registrations it would be helpful to have Class Status tables to monitor the state of the registration or intent to register. Which would mean you would have to upgrade to Pro edition. 

.One other thought. within Method, you could set up a screen to retreive the additional data from the Contacts table and display it there.  Set up a grid  and then use the "export to excel" action so can be printed. 

RD Documention for can be found at: http://help.devexpress.com/#XtraReports/CustomDocument2162    Here is the caution:  Not all of the features in this documentation are available to Method.   But it does give one a good basic foundation for dealing with more complex reporting needs and RD properties.  It is more technical than the webinars on reporting done by Method.  

With the Method basic subscription, you can add fields to existing tables. But you can not add new tables.  However, I would think that with registrations it would be helpful to have Class Status tables to monitor the state of the registration or intent to register. Which would mean you would have to upgrade to Pro edition. 

Anyhow...good luck and feel free to contact a solution provider, like myself, if you want more help. 

Fran

Thanks fran for posting.  You have given some good advice and guidance. I'm still working on being fluent in the Report Designer.

Just to re-iterate, Report Designer does need the fields to be on the table that the report is pointing to, to get the information.  There is a 'Retrieve Value from Table' action that can use basic SQL to retrieve table data.  You can try putting the action before the 'Generate Report' action and insert data into holder fields.  Out Help Centre article on the action is here.

It does sound like a job for a Solution Provider or Method's Dedicated Consultants.  You can give any of them a try.

Fran ane Greg,

First of all Happy New Year.  Secondly, thanks for all the feedback.  This has been really helpful.

Today I think I have come up with a solution that I am going to try to implement.   I have done some simple proof of concept and it seems to work.

Started by setting up a new table that has mutilpe simple text fields.  This new table will ultimately be used in the Report Designer as the data source.

The idea is to create a simple screen that has a single button or add that button to existing screens.  When the user clicks on the button it will clear the special table of all the records using the Delete Record from Table action. Then I loop thru the main table (in my case the Transaction Table) and collect one record at a time and use the Retrieve Value from Table to get any of the data that is in the ancillary files (like the contact file).  I then used the Insert Records into Table to populate the special table.  Once completed I can use the generate report and get the results I need. 

The key for me is this gives me a simple solution that can be used for any situations that come up and still use the RD for the formating of the reports.

One question I have is can anyone recommend an existing table that I can use for this rather than having to go to Pro for just this one new table.   Obviously we don't want to use any of the tables that integrate with QB.

Again, many thanks for all your help.

Stuart

Stuart,

I did not want to imply that you can not add fields to QB tables, you can... That is the beauty of Method, What goes to QB syncs, what does not stays in Method..

But the Transaction table is a special QB table.  it is different than the rest. And that is why I caution about mucking with it. 

Your idea of a special table for reeporting will work but yu would need to upgrade to Pro. There may be other thoughts on this approach and you will need to either call Method for consulting time or call one of the Method Solution Providers for appoaches.  Sometimes a 1/2 to an  hr conversation will save you soooo much time. 

Fran,

Understood about the QB Tables and the Transaction Table being special.

I have played some more and am really comfortable with the approach I outlined.   The only question is whether I have to move to Pro and the added monthly expense for just one table.

I have spent some time looking at some of the tables I am not using (like CaseOrigin, CasePrioriyt, etc) to see if I can use one of the them.   I came across the DataExtDef and wondered if you can tell me where it is used.  It says that it is a specialized accounting table but there are no fields in it?  That might be the pefect table for me to use.

Stuart

By the way, if you are golfers a(or not) and would like to see where we live, be sure to turn on the golf channel and watch some of the Hyundai Tournament.

Stuart,

You are treading on dangerous ground hijacking CRM tables for your reporting purposes.  not worth the $15month charge difference.

Fran,

I agree which is why I asked if anyone knows what DataExrDef does as it does not have any fields in the table.

Unfortunately, for a small business the $15 per month per user does become a nunmber to be concerned about.

Stuart

Ok Stuart.... one last thought for you to try...Iknow how frustrating it can be to not e able to move forward... in report designer, try using the contacts table as yur master table and see what detail tables are avvailabe.. as Greg mentioned earlier tables with relationships should show......  

Mahalo.....  

Hello Stuart,

    I would not recommend changing the DataExtDef table.  I don't know what it is for, but it may be there for processes unseen and you could, in essence, break your table and incur problems. 

- Greg