Forum Discussion

BenGillihan's avatar
BenGillihan
Qrew Cadet
6 years ago

Lookup Question

I am trying to connect transactions in two tables and I need some help figuring out how to do it. 

We have a list of all our employees' credit card transactions that we import from BOA/Chase.  The key field is the record ID.   We have another table that has all employees timecard data with dates worked.  Again the key field is just the record ID.  I would like to be able to verify that an employee was working on the day that a credit card transaction occurred.  I would like to do this in the credit card transaction table, so we get a flag if they didn't work that date.  I have created another table that I called employees that can pull reports from both the credit card transaction and timecard tables, but I can't think through how to get to the next step.  I feel like I could easily do this in Excel by concantenating the date and name for each line in each table and then doing a vlookup to find a match, but I am not sure how or if this is possible in QB.

Thanks!

Ben

------------------------------
Ben Gillihan
------------------------------
Relationships
  • BrianCafferelli's avatar
    BrianCafferelli
    Quickbase Staff
    6 years ago
    Hi Ben, assuming your combination of date and name is unique, you should be able to do this. You'll want to relate the timecards as a parent table of transactions, then add a lookup field from timecards to transactions. If you look up a field that's present / required on all timecards, then on the transacations table you can check to see whether the lookup field has a value. If it has a value, than the person was working on the day of that credit card transaction. If the lookup field is blank, than they were not working. Here's how you can set it up:

    1. Create a text formula on the timecards table to concatenate the date and name (maybe call it Timecard Key Field).
    2. Change the field type of Timecard Key Field from Formula - Text to just Text, and make it the key field for the table. This allows you to initialize this field, and depending on how you enter timecards as time goes on you could import your date-name combination into this field.
    3. Relate the timecard table as parent of the transactions table.
    4. Choose any required field on the timecards table, and add it as a lookup field on the transactions table.
    5. (optional) Create a formula checkbox field (maybe called something like Did employee work on day of transaction), that looks something like this:

      If([lookup field] = "", false, true)​

      One thing I need to add is that Quick Base is not PCI compliant, so you cannot store credit card numbers in a Quick Base app. Please make sure your credit card transaction data does not include credit card numbers.


    ------------------------------
    Brian Cafferelli
    ------------------------------
    • BenGillihan's avatar
      BenGillihan
      Qrew Cadet
      6 years ago

      Unfortunately, employees often have more than one payroll record per day,  so I can't make the key name and date.

      We aren't storing the whole CC# in QB.  Just the last few digits to identify which employee it is.  A good point to remember though.

      Thanks.



      ------------------------------
      Ben Gillihan
      ------------------------------