I’m building what should be a simple database and interface but I’ve hit a couple of obstacles that have got me stumped.
My design was to have a single layout for the user showing two portals. The system would keep track of the stock of medication at a small care home. One portal would list each medication in stock and allow the user to filter the display by a resident’s name, medication name, and supplier. The second portal is linked by the primary key of each medication stock record and will be used to store the history of transactions that affect the stock levels, such as receipt from pharmacy, issued to residents and returned to pharmacy. The transaction portal would also show the date and staff ID associated with each transaction.
My first obstacle was that the layout did not allow a portal to be displayed unless it was linked to a “parent” table. My care home is split into two units, so I solved that problem by making a Unit table and layout (with two records) and placing the two portals there. The medication records in stock are linked and matched by the unit name and the primary key of these stock records links to the transactions records in the second portal.
So far so good, my testing with mock-up data showed that I could click a record in the stock portal and the transactions portal would list the history for that item.
However, I have a script for adding new medication records that ends by hopping over to the transaction portal and creating a received transaction record. I expected to see the primary key of the active portal row record but it always uses the key of the first record. When I get current record ID the systems returns that of the unit table rather than the stock record as I expected..
I can work around this by overwriting the primary key with the portal row I want, but I’d like to understand where I’m going wrong.