How to structure look-up tables

 
2
1
0

My data model has 3 tables - 2 dimension tables and 1 fact table.  For example, to use data that you're familiar with, I have "ExpenseClaims" (dimension), "ExpenseClaimItems" (fact) and "ValidClaimItems" (dimension) tables.  The fact table only has keys to each of the dimension tables.  Having created a row in ExpenseClaims, I want to select ExpenseClaimItems entries from the table ValidClaimItems.  I was able to use a Form Grid to populate my fact table using key values from ValidClaimItems, but I can't work out how to populate my fact table by selecting one or more rows from ValidClaimItems.

I'm new around here!  Please help.

    CommentAdd your comment...

    2 answers

    1.  
      1
      0
      -1

      Thank you for your response, Hugo.  I managed to use the look-up table OK, but I'd like to be able to store more than the foreign key in my fact table, so that when I view an expense claim and it's items, I am able to view other information from the ValidClaimItems table.  Perhaps my question might be clearer if I give you some examples of the field names in each table (these aren't the same as the distributed app):

      tblExpenseClaims: EC_ClaimNo; EC_ClaimDate; EC_ClaimDesc

      tblExpenseClaimItems:  EI_EC_ClaimNo; EI_VC_ItemNo; EI_VC_Category; EI_Amount; EI_Reason

      tblValidClaimItems:  VC_ItemNo; VC_Category; VC_ItemDesc; VC_Approver

      So, in tblExpenseClaimItems,  EI_EC_ClaimNo links to EC_ClaimNo, and EI_VC_ItemNo links to VC_ItemNo.  

      I then want to design an expense form which shows not only the information from the ExpenseClaim table, but also each ExpenseClaimItem entry, consisting of EI_VC_ItemNo, EI_VC_Category, EI_Amount, EI_Reason, VC_ItemDesc and VC_Approver.  

      One way might be to add VC_ItemDesc and VC_Approver to tblExpenseClaimItems, but how do I populate these fields having displayed the tblValidClaimItems entries to the user?

      I hope that explains it clearly enough!!

        CommentAdd your comment...
      1.  
        1
        0
        -1

        Referring to the HR Expenses Claim app, instead of selecting from hardcoded category when adding individual claim item, you can have it to look up in other table (your ValidClaimItems table) using Default Form Options Binder. You can now make reference to your needed table.

          CommentAdd your comment...