[Nitin Thokare]

I have created following three tables:

 

$createTableQuery="CREATE TABLE IF NOT EXISTS ITEM(
Id INT auto_increment,
Prefix VARCHAR(20),
Code VARCHAR(50),
Name VARCHAR(50) NOT NULL,
Description VARCHAR(255),
ReceivingUnit VARCHAR(20),
BillingUnit VARCHAR(20),
Type VARCHAR(50),
Category VARCHAR(50),
SubCategory VARCHAR(50),
UNIQUE(Code),
PRIMARY KEY(Id)
)";


$createTableQuery="CREATE TABLE IF NOT EXISTS PO(
Id INT auto_increment,
Date DATE,
QuoteId INT,
VendorId INT NOT NULL,
PaymentTerms VARCHAR(255),
Remark VARCHAR(255),
PRIMARY KEY (Id),
FOREIGN KEY(QuoteId) REFERENCES RequestForQuote(Id),
FOREIGN KEY(VendorId) REFERENCES VENDOR(Id)
)";


$createTableQuery="CREATE TABLE IF NOT EXISTS POItems(
BillId INT,
ItemId INT,
Quantity INT DEFAULT '0',
Rate DECIMAL(10,2)  DEFAULT '0.0',
ExciseDutyPercent DECIMAL(5,2) DEFAULT '0.0',
VAT DECIMAL(5,2) DEFAULT '0.0',
CST DECIMAL(5,2) DEFAULT '0.0',
ServiceTax DECIMAL(5,2) DEFAULT '0.0',
DiscountType VARCHAR(20) DEFAULT '0.0',
Discount DECIMAL(10,2) DEFAULT '0.0',
Remark VARCHAR(255),
PRIMARY KEY(BillId, ItemId),
FOREIGN KEY(BillId) REFERENCES PO(Id),
FOREIGN KEY(ItemId) REFERENCES ITEM(Id)
)";

 

I added items in ITEM table. Now I want to add new in PO table. PO contain multiple Items from ITEM table, which gets added in POItems table.

 

I want to add new in PO table by adding PO details and also add multiple Items in it with other details from POItems table for each item.

 

Is it possible to do this using relation_n_n in GC? If yes, how can I do this?

 

Also while viewing(read) PO, I want to view all details along with data (rows) from POItems where "POItems.BillId == POId".

 

Is it possible to do this? If yes, how?

 

How can I change add/edit/read forms to achieve this?

[Paul Savostin]

Hi. Wellcome to GC forum! As I know there is no built in like you want future in GC. On my previous project i tied 2 tables in one edit/view output and storing data with callbacks methods.

In list state i wrote custom model. You can find examples here on the forum from author of GC - John Skoumbourdis
 

Just look up better:)

I ofcourse I can always write custom model for few tables in all states read?edit and list. Sorry but with that i don't help you much:(

[Nitin Thokare]

Hi Paul Savostin,

 

Can you give any sample model to show 2 tables in one edit/view output?

[Paul Savostin]

Hi, I told you before that i wrote custom model only in list view cause it easy. For read, edit, update states I used callbacks! That's all i know. Sorry if that not the answer you expect:)