Oracle APEX 4.1 Master detail form question

darren.w

Hi Guys,

Total noobe, hope i have posted in right section. can you can help me out with a project I am working on in Oracle Apex 4.1 with 11g XE

I am sure this is a simple enough issue but I just can’t see the solution.

I have a table called ‘STOCK’ this contains all new parts delivery details.

‘STOCK_ID’ NUMBER PK
‘DELIVERY DATE‘ DATE
‘PART’ VARCHAR2
‘PART_SN’ VARCHAR2
‘PART_AN’ VARCHAR2
‘INSTALLED’ CHECKBOX

I have a table that list all the current physical locations of hardware for example:

ID, Till number, store location etc…

I have an empty table to list the devices and modification to the hardware in these store locations.

Move Date, Device, Device Serial number, Device Asset Number, Comments
I have a report & form that displays all the current physical locations of hardware and a master detail form linked to this to add devices and modifications to each store location.

I have a LOV setup on the Devices column that is based on a simple select query:

SELECT PART from STOCK
ORDER BY 1

This provides me the list of parts in the stock table.

What I need to figure out is how do I get the data from the ‘STOCK’ table when I select a part in the LOV to auto populate ‘DEVICE_SN’ ‘DEVICE_AN’ in my master detail tubular form.

I also need to figure out how to identify the part because by default the LOV only displays one column and I have no idea which part I am selecting other than by name. I could ORDEY BY ‘PART_SN’ I suppose and use a popup LOV and scan a barcode into the search box?

I also would like to reduce the number of records displayed in the LOV by using the ‘INSTALLED’ checkbox i.e. if checked this part has been used and will not be available for selection.

The ‘PART_SN’ field can be duplicated as we often get the same part back again as a replacement after repair but this should be valaditated against i.e if the parts is ticked as installed and not available for selection in the list it can be added again this could possibly be validated based on the ‘DELIVERY DATE‘?

I hope this make some sense to you guys if you need any further details let me know.

Cheers

Darren

Tom Dunne

darren.w wrote: »

Hi Guys,

Total noobe, hope i have posted in right section. can you can help me out with a project I am working on in Oracle Apex 4.1 with 11g XE

I am sure this is a simple enough issue but I just can’t see the solution.

I have a table called ‘STOCK’ this contains all new parts delivery details.

‘STOCK_ID’ NUMBER PK
‘DELIVERY DATE‘ DATE
‘PART’ VARCHAR2
‘PART_SN’ VARCHAR2
‘PART_AN’ VARCHAR2
‘INSTALLED’ CHECKBOX

I have a table that list all the current physical locations of hardware for example:

ID, Till number, store location etc…

I have an empty table to list the devices and modification to the hardware in these store locations.

Move Date, Device, Device Serial number, Device Asset Number, Comments
I have a report & form that displays all the current physical locations of hardware and a master detail form linked to this to add devices and modifications to each store location.

I have a LOV setup on the Devices column that is based on a simple select query:

SELECT PART from STOCK
ORDER BY 1

This provides me the list of parts in the stock table.

What I need to figure out is how do I get the data from the ‘STOCK’ table when I select a part in the LOV to auto populate ‘DEVICE_SN’ ‘DEVICE_AN’ in my master detail tubular form.

I also need to figure out how to identify the part because by default the LOV only displays one column and I have no idea which part I am selecting other than by name. I could ORDEY BY ‘PART_SN’ I suppose and use a popup LOV and scan a barcode into the search box?

I also would like to reduce the number of records displayed in the LOV by using the ‘INSTALLED’ checkbox i.e. if checked this part has been used and will not be available for selection.

The ‘PART_SN’ field can be duplicated as we often get the same part back again as a replacement after repair but this should be valaditated against i.e if the parts is ticked as installed and not available for selection in the list it can be added again this could possibly be validated based on the ‘DELIVERY DATE‘?

I hope this make some sense to you guys if you need any further details let me know.

Cheers

Darren

It's going to be next week before I am near an Apex install, but for now I would say have a look at the extensive Oracle tutorial on Apex. I am 95% sure there are examples of what you are asking in there. It's a PDF on the Apex section of Oracle.com.

darren.w

Thanks Tom for the reply i will have another trawl through that pdf file it is really good but i do get a bit lost in it

darren.w

Hi guys,

Can anybody help me this part?

"What I need to figure out is how do I get the data from the ‘STOCK’ table when I select a part in the LOV to auto populate ‘DEVICE_SN’ ‘DEVICE_AN’ in my master detail tubular form."

Is it a query or procedure or what should i be looking towards?

Would really appreciate if you could point me in the right direction.

Cheers,

Darren

darren.w

Can you point me in the right direction

Table 1:
=====
Part_ID (PK)
Delivery_Date
Part
Part_SN
Part_AN
Installed (Y/N) (Checkbox) thats anbother question)
Sub_ID (FK)


Table 2: (master detail form)
======

Sub_ID (PK)
Install_Date
Part (LOV created from part in Table 1)
Part_SN
Part_AN
Installed_by (LOV)
Call_Ref
Commkents.

I can populate the 'Part' cell from the LOV but how do i assign the 'Part_SN' & 'Part_AN' from table 1 to the 'Part_SN' & 'Part_AN' cells in row 2