Business Central

Thursday 26 March 2015

How to Create your own table and make a report of it..!!

Always it is necessary to create a table with your own scenario because that will improve your creativity mind and will also give you a deep knowledge of how to learn things quickly. So, Now I'm going to show a scenario based on movie list.

I'm going to explain how to create a table along with how to create a report linking two tables


1.       Create table with name MovieMakingTable with fields including (Serial no., movie name, sensor certification,audience rating, production cost)
2.      Set Serial no. and production cost as primary key, because you need to relate two tables based on serial no. and movie name can be repeated with  d/f serial no. based on different productions. In order to repeat names for same serial no. we also take sum of productions as primary key.
3.      Let the properties of this table be not disturbed.

1.       Create MovieMaking table with fields including(film code, director name, email, ph. no, profit, sum of productions,director, producer name , shooting locn and no. of ppl involved)
2.      Set film code as primary key.
3.      In Salary field properties,
Go to field class and select FlowField(to sum the production cost of each directory of movie released table we chose flow field)
4.      Now, update CalcFormula by choosing assit edit
Select method as Sum, table as –MovieReleased table
Field-production cost
Table Filter-Based on serial code, type-field, Value-film code
5.      Now, the code you ‘ve developed will sum up the table of each director and will display the sum in another table.

Now, we should filter the table displayed in moviemaking table based on movie rating and sensor certification.

1.       Come back to Movie released table. Create a field called type and choose option as datatype (give option string property with values U,U/A,A).
2.      Save and come back to MovieMaking table. Create two fields called Audience rating with datatype as option(set option string property with values 1,1.5,2, so on upto 5) and create another field called Sensor certification with its data type as option(set option string property with values U,U/A,A).
3.      Select flow filter in field class property for these two newly created fields)

1.       Come back to sum of production field of movie making table and go to its calcformula property, go to table filter, Add two additional fields called search based on certification and search based on rating, type is option. Click Ok and Save.
2.      Run the Moviemaking table type ctrl+shift+F3 add filter select either (select by date or select by month or both) and you’re done with the task.


MovieList Table- 2 different tables  that has been created



 Create a Table link report for Customer Table and Customer Ledger Table with your selected columns from these two d/f table. Sort with only date column by removing time and calculate net amount and total amount.
     
    
  1.       Add new Report, Create DataItem and select DataSource table as Customer, select properties of DataItem Set DataItemTableView Assist Edit
Select key as No., order-ascending, Table Filter-(field-balance, type-filter, value>0)
   2.      ReqFilterFields property – No., Name
   3.      CalcFields Property-Balance(LCY),save and compile.
   4.      Select FieldMenu and select columns customer no.,name,name2 and balance(LCY).

   1.       Create another DataItem with data source as CustomerLedger Entry table.
 2.      Go to properties of DataItem, choose DataItemLinkReference as Customer and DataItemlink Field-Customer No., Reference Field-No. 
  3.      Select field menu and add columns(entryno., customer no., posting date,amount) save and compile.
  4.      Goto View-Layout.
  5.      Drag a table and give tablix properties-select datasetname as datasetresult.
  6.      Create a header row with customer no. ,name, name1 and balance details)
   7.      Next row-add parent header values.
   8.     Next row-add child header(entry no.,custno, posting date, amount).
   9.      Add details in next row.
  10.  Calculate individual total amount and net amount. Finally save, compile and preview. To calculate sum(double click on column and add a text as sum).
Ex. [Sum(Amount_CustLedgerEntry)] compile and save
  11.   If you need to filter by date and remove time, select posting date details column, on the right bottom corner on properties window go to Number header, Format give value as d. save and compile.
Customer Report created between two tables









No comments:

Post a Comment