1 Reply Latest reply on Aug 26, 2012 7:04 PM by cortical

    Help Needed Relation & Calculation


      Hello Experts,


      This is my long time problem, kindly help me or suggest what should Im going to do in order that my three fields (Re-order_Alarm, Retest_Alarm, Expiry_Alarm) will work correctly. Please find attached file for you to see the context. Thank you very much to any suggestions or help.



        • 1. Re: Help Needed Relation & Calculation

          Hi thong,



          It would seem to be the count aspect that is the nub of your issues.


          You are using  the primary key (PK) in the rel, and so you are only ever going to have a valid REL , when there is a record, and hence only ever a value of 1 (ie. self).


          But that is not the real issue.



          What you are attempting to do is count material_lot_no by material_name, but  by using the material_id rel, there will only ever be one related (self) record


          i.e you have  category and item data, need to count by cataegory (material) , but are using the item id for the count


          Now normally, one would have a table of Materials ( IsoPropyl, Ethanol..) and a second table of actual brand/size/grade actual physical entities. So a winchester of ethanaol GP would be a different record to Ethanol Spectro etc.

          What you are missing is cataegorising what are line items by a parent (material) category.


          So a parent child structure is really required. The child table , actual physical entitites, would have its own primary key ( inventory_id or whatever), and also a foreign key material_id.


          The count in Materials would then use a  rel to inventory using material_id.

          Or if you prefer MaterialType and Material tables.


          As it is, you are trying to put all your winchesters in one table. You might get away with this if you were manually entering the material_id, but then it would not be a PK.


          What you could try, if you want to maintain your simple single table structure, is creating a a REL between MaterialName and using that for the count REL.  Not what I would at all recommend for a real database ( ie name based rel) as fraught with potential issues, and is just plain poor design. Using the material code would be no better. But this in the first instance will illustrate the count apsect.



          The PK for a table should ALWAYS be defined as ; serial, unique, not null, immutable. You have serial as the only definition on material_id


          1). vaildation not empty, unique value, uncheck allow user override

          2). auto enter tab; serial (  and a alpha prefix helps clarity in numerous instances e.g M0000001)  and check probibit modification during data entry




          You are on the right track looking to use material_id, but the single table structure is the limitation.

          While you could craete a value list for material_name, and have mutiple ethanol records etc, it would only be a half way house. Create a new table for materials, and redefine the current material table material name field as material_id. A popup menu and value list can then be used to enter the id but display the related name.


          As for the colour embedding in the calc definitions, making life harder for your self, just define conditional formats on the text annotation flag fields ( eg expired_alarm_text / Expired Alarm)

          e.g. If( Get ( CurrentDate )  > Supplies::Expiry_Date  ; 1; 0) ; file red, text white


          A couple of peripheral observations;

          the naming convention you have adopted is good, and the keying name material_id with _id suffix is what I use too. I would drop the uppercase in field names, adds nothing to readability and just requires extra key fingering