Studio7t7
New Member
Posts:8
 |
| 20 Jun 2012 10:16 PM |
|
Hi guys,
I have an application for selling items.Now I need to implement inventory kind of thing here.There are 3 main tables.Production,Purchase and Sales.Here is the relationship
I have created computed field for each 3 tables and tried to get the quantity value.
ProductQuanity,PurchaseQuantity,SalesQuanitiy
Code is like this
partial void ProductQuantity_Compute(ref int result)
{
result = Convert.ToInt32(this.intQuantity.ToString());
}
This returns me quantity for each item.So as I need to show this in my main Products Screen I have added these
3 fields in to the screen using designer.I tried to run and see but it shows me empty values
If it shows correctly I can calculate it like this
Balance = ProductQuanity + PurchaseQuantity - SalesQuanitiy
Could you please let me know whats wrong here or how to implement this kind of requirement correctly
Thanks |
|
|
|
|
hgminerva
Veteran Member
Posts:98
|
| 21 Jun 2012 12:37 AM |
|
Put the three computed fields in your tblProduct and then do a LINQ to query the Quantity of each transaction to get the result:
dim totalQuantity as integer=0
data= (from o in me.dataworkspace.applicationdata.trntransactionout where o.strProductNo.equals(me.strProductNo))
if not isnothing(result)
totalQuantity=0
for each item in data
totalQuantity= totalQuantity+ data.intQuantity
next
end if
result=totalQuantity
|
|
| http://hgminerva.wordpress.com |
|
|
Studio7t7
New Member
Posts:8
|
| 21 Jun 2012 08:59 AM |
|
Hi,
I tried this and i dont have strProducNo
data= (from o in me.dataworkspace.applicationdata.trntransactionout where o.strProductNo.equals(me.strProductNo))
Any ideas? is that something with relationships? I tried to create a relationship but it says cannot add relationship because they are in same container.
Thanks
|
|
|
|
|
hgminerva
Veteran Member
Posts:98
|
| 21 Jun 2012 05:08 PM |
|
Posted By Studio7t7 on 21 Jun 2012 09:59 AM
Hi,
I tried this and i dont have strProducNo
data= (from o in me.dataworkspace.applicationdata.trntransactionout where o.strProductNo.equals(me.strProductNo))
Any ideas? is that something with relationships? I tried to create a relationship but it says cannot add relationship because they are in same container.
Thanks
Base on your relationship screen shot you have a strProductNo that is related to your transaction tables.
I assume that all the tables in the screen shot are now your Data Sources, then i expect that they are all related. Then the linq data would not be a problem.
If these tables are external data sources then you cannot create a relationship inside lightswitch.
If the computed field is a problem you can also result to view and link it up to lightswitch. Much faster this way. |
|
| http://hgminerva.wordpress.com |
|
|
Studio7t7
New Member
Posts:8
|
| 21 Jun 2012 11:11 PM |
|
Actually I have connected LS application to SQL server which is in USA.I dont know this is the best way to do it?
please explain what did you mean by this
If the computed field is a problem you can also result to view and link it up to lightswitch
I am not really an expert in LS this is my first LS project 
Thanks |
|
|
|
|
hgminerva
Veteran Member
Posts:98
|
| 22 Jun 2012 07:00 PM |
|
Sorry I'm so vague with my answers. Anyways, I don't have a document example on inventory, what I have is the a debit/credit memo adjustments on a sales invoice, plus when you debit then minus when you credit, i think it is more or less similar to inventory in and out. http://hgminerva.wordpress.com/2012...gthswitch/ |
|
| http://hgminerva.wordpress.com |
|
|
Studio7t7
New Member
Posts:8
|
| 09 Jul 2012 03:23 AM |
|
Posted By hgminerva on 21 Jun 2012 06:08 PM
Posted By Studio7t7 on 21 Jun 2012 09:59 AM
Hi,
I tried this and i dont have strProducNo
data= (from o in me.dataworkspace.applicationdata.trntransactionout where o.strProductNo.equals(me.strProductNo))
Any ideas? is that something with relationships? I tried to create a relationship but it says cannot add relationship because they are in same container.
Thanks
Base on your relationship screen shot you have a strProductNo that is related to your transaction tables.
I assume that all the tables in the screen shot are now your Data Sources, then i expect that they are all related. Then the linq data would not be a problem.
If these tables are external data sources then you cannot create a relationship inside lightswitch.
If the computed field is a problem you can also result to view and link it up to lightswitch. Much faster this way.
I have added the View in my SQL database and calculation is fine there.When I try to update the datasource newly created View is no there to import 
What can be the reason and how can I import it?
Thanks |
|
|
|
|
hgminerva
Veteran Member
Posts:98
|
| 10 Jul 2012 10:40 PM |
|
Posted By Studio7t7 on 09 Jul 2012 04:23 AM
Posted By hgminerva on 21 Jun 2012 06:08 PM
Posted By Studio7t7 on 21 Jun 2012 09:59 AM
Hi,
I tried this and i dont have strProducNo
data= (from o in me.dataworkspace.applicationdata.trntransactionout where o.strProductNo.equals(me.strProductNo))
Any ideas? is that something with relationships? I tried to create a relationship but it says cannot add relationship because they are in same container.
Thanks
Base on your relationship screen shot you have a strProductNo that is related to your transaction tables.
I assume that all the tables in the screen shot are now your Data Sources, then i expect that they are all related. Then the linq data would not be a problem.
If these tables are external data sources then you cannot create a relationship inside lightswitch.
If the computed field is a problem you can also result to view and link it up to lightswitch. Much faster this way.
I have added the View in my SQL database and calculation is fine there.When I try to update the datasource newly created View is no there to import
What can be the reason and how can I import it?
Thanks
You cannot import it if there is no primary key. Make sure that at least one of the field is set to Not Null so that ligthswitch will import it. |
|
| http://hgminerva.wordpress.com |
|
|
Studio7t7
New Member
Posts:8
|
| 14 Jul 2012 10:32 PM |
|
Hi,
This is my View
USE [laub_Web]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[ss_ls_remain_Stock]
AS SELECT DISTINCT
ou.strProductNo, SUM(S.intQuantity) AS stock, SUM(ou.intQuantity) AS quantityOut, SUM(I.intQuantity) AS quantityIn, SUM(ou.intQuantity) - SUM(I.intQuantity)
AS balance, SUM(S.intQuantity) + SUM(I.intQuantity) - SUM(ou.intQuantity) AS bal
FROM laubsup.SS_tblTransactionOut AS ou CROSS JOIN
laubsup.SS_tblTransactionIn AS I INNER JOIN
laubsup.SS_tblCanning AS S ON S.strProdNo = ou.strProductNo
GROUP BY ou.strProductNo
GO
But still it doesn't recognized by LS?
Any ideas
Thanks |
|
|
|
|
Michael Washington
Posts:990
|
| 15 Jul 2012 04:21 AM |
|
LightSwitch needs to be able to determine a primary key to import a view as a table. A column or combination of columns that are unique for a record. |
|
-----------
Michael Washington
http://LightSwitchHelpWebsite.com |
|
|
hgminerva
Veteran Member
Posts:98
|
| 15 Jul 2012 07:22 PM |
|
Make sure that your strProductNo is set NOT NULL it its source table. |
|
| http://hgminerva.wordpress.com |
|
|
Studio7t7
New Member
Posts:8
|
| 16 Jul 2012 02:28 AM |
|
Hi,
I have made strProductNo not NULL in my tables and updated the data source
But still its not showing?  |
|
|
|
|
hgminerva
Veteran Member
Posts:98
|
| 16 Jul 2012 05:40 PM |
|
Ligthswitch should be able to determine automatically a field that is not null to be a primary key. So far I don't have problem with this, I've tried it with MS SQL and Oracle for both views and tables. Anyways, please check if you have full rights to the Views using your connection strings. Worst case scenario is that you have to create a WCF RIA Service and put a flag to the strProductNo. |
|
| http://hgminerva.wordpress.com |
|
|
Studio7t7
New Member
Posts:8
|
| 23 Jul 2012 10:16 PM |
|
Posted By hgminerva on 16 Jul 2012 06:40 PM
Ligthswitch should be able to determine automatically a field that is not null to be a primary key. So far I don't have problem with this, I've tried it with MS SQL and Oracle for both views and tables. Anyways, please check if you have full rights to the Views using your connection strings. Worst case scenario is that you have to create a WCF RIA Service and put a flag to the strProductNo.
Hi hgminerva,
I have no idea about WCF RIA service.Is there any 3rd party control where I can use for this?
Thanks |
|
|
|
|
hgminerva
Veteran Member
Posts:98
|
| 23 Jul 2012 11:20 PM |
|
Posted By Studio7t7 on 23 Jul 2012 11:16 PM
Posted By hgminerva on 16 Jul 2012 06:40 PM
Ligthswitch should be able to determine automatically a field that is not null to be a primary key. So far I don't have problem with this, I've tried it with MS SQL and Oracle for both views and tables. Anyways, please check if you have full rights to the Views using your connection strings. Worst case scenario is that you have to create a WCF RIA Service and put a flag to the strProductNo.
Hi hgminerva,
I have no idea about WCF RIA service.Is there any 3rd party control where I can use for this?
Thanks
You don't need any 3rd party: http://hgminerva.wordpress.com/2012/06/21/how-to-create-a-vb-wcf-ria-services-for-microsoft-lightswitch-to-aggregate-or-join-tables-at-the-same-time-pass-a-parameter/
|
|
| http://hgminerva.wordpress.com |
|
|