Medicines Sensor?

Configuration
1

Hi All,
Yep, I’m very new to HA and would like some help.
Firstly I am disabled and partially sighted so reading masses of confusing documents is painful.
I am trying to add a Notice / Area ? that tells me when my medications are about to run out.
I have created a basic Sqlite3 table that contains the columns:
Med_Name
Pack_Quantity
Updated_Date
Daily_Dose
I know I need to Divide Pack_Quantity / Daily_Dose to get the number of days till I run out of that particular drug.
I then need to add that to the Updated_Date to get the actual date the drug will run out.
Also if I subtract 7 from the actual date that will give me 7 days to obtain a new pack. 7 Days is the maximum the Pharmacy will alllow on some of the drugs.

I created a test sensor in configuration.yaml


sensor:
  - platform: template
    sensors:
      julian_fudge_sensor:
        friendly_name: "Julian's Fudge Sensor"
        value_template: "{{ 1234 }}"
        unit_of_measurement: "units"
        unique_id: fudge_sensor

This proves I can at least create and display a sensor.

My question is do I make a python script that interrogates the Sqlite table and calculates the information that I need or directly interrogate the Sqlite db and configure the return values, push the date to MQTT and pull it from there?
Whichever is the better solution how do I create the template ? With all of the code in it {{ }} or in a file and pass the data to the template {{ }} ?
If someone could please indicate how to proceed I will be indebted.

Thank you and kind regards,
jB :sunglasses:

2

How many different medicines are you looking to track ?
If its not hundreds, perhaps use HA’s helpers to store the data instead, then its trivial to create templates which days until you run out.

3

I would also choose a mentioned way “use HA helpers”.
Assume you have 5 input helpers for those parameters.
Then assume you have some amount of template sensors (one per a medicine):
state - some most important parameter,
attributes - equal to those helpers.
These sensors would have same structure but using different helpers as source.
And you may display these template sensors in a table-like card (custom flex-table-card, stock Markdown).

You may even have one helper per a parameter, not for every medicine. And some input_select helper (filled with names of medicines) may be used to select which particular template sensor should be updated when a parameter’s helper is changed.

And automations tracking a particular attribute for all medicines’ template sensors will alert you about necessary actions.

Hope my explanations are clear.

4

Thank you both for your input.
As you both agree that helpers is the way to go, I will try and see what I can pull up using helpers.

If it all works I will report back.
Many thanks and kind regards.
jB😎