Trying to get the information from one table based...

Itanova_Pastry · 11-12-2020 11:23 AM

Hello everyone,

First off all I’ll apologize in advance if my grammar is a little bit odd, English is my second language.

I’m working on an app for a pastry shop and I’m stuck trying to get the information from one table based on two conditions in another table.

The tables:
Recipe: a collection of all the recipes of the store.
Batches: a recollection of every time they bake, and the financial of the bake.
Batch detail: Store the details of the Batch made, recipes used, amount of ingredients used for this particular production. The deal is they can use several recipes in one Batch.

And the table that I need to fill.
Sales details: which is a recollection of the sales done by batch to compare the expenses and the income.

All the tables are connected to each other with REF relationships.

Now, I want to fill the column “recipe used” taken from the table “Batch detail”, but this will be based by the “Batch date” which belongs to the table “Batches”

So, what I want is to narrow down the entry options for the sale to only those recipes that where baked that day of the Batch the user selects.

I have try different options and angles using lookups and selections but haven’t been unsuccessful on it. I’m kinda new on this type of selections.

Steve

What expressions have you tried?

Itanova_Pastry

Hi Seteve, Sorry for the delay, my laptop got issues and had to fix it.

So far these, I have read the collections in the help section and tried to figure it out:

SELECT(BatchDetail[BchDtl_Rcp_ID],([BchDtl_Key] = [_THISROW-1].[SlsDtl_Bch_ID]))

SELECT(BatchDetail[BchDtl_Rcp_ID],([BchDtl_Rcp_ID], = [_THISROW-1].[SlsDtl_Rcp_ID]))

LOOKUP([_THISROW].[SlsDtl_Bch_ID],“BatchDetail”,“BchDtl_Bch_ID”,“BchDtl_Rcp_ID”)

ANY(
SELECT(
BatchDetail[BchDtl_Rcp_ID],
AND(
FILTER(Batches,[Bch_Key] = [_ThisRow].[SlsDtl_Bch_ID]), FILTER(BatchDetail,[BchDtl_Rcp_ID] = [_ThisRow].[SlsDtl_Rcp_ID])
)
)
)

All the expressions, are on the SlsDtl_Rcp_ID column of the SalesDetail table.

This is The full Table structure

Steve

How is the user selecting a batch?

Itanova_Pastry

The idea is for the user, at the time of the sale select a batch day and get all the things (recipes) that were baked at the time. This way it can be tracked the performance of each batch.

In the input form for the table there is just a calendar, and each row has a unique ID key.

Steve

So given a batch date, list all recipes baked that day?

Itanova_Pastry

That’s the idea.

When the customer bakes something, it records all the items if that day’s production.

When there is a sale, we want to know what day the items sold were made.

Table logic goes like this

Batch table, gets de “date”
Batch Detail table, gets the “recipes” (just the ID)
Recipes table, gets the recipe name.

Sales detail table, we want to put in a date and narrow down the options to those items baked that day.

Steve

Distinct recipies of batches baked the day of this sale:

SELECT(
  BatchDetail[BchDtl_Rcp_ID],
  IN(
    [BchDtl_Bch_ID].[Bch_DateStamp],
    SELECT(
      Sales[Sls_TimeStamp],
      ([Sls_Key] = [_THISROW].[SlsDtl_Sls_ID])
    )
  ),
  TRUE
)

Names of distinct recipes of batches baked the day of this sale:

SELECT(
  Recipes[Rcp_Name],
  IN(
    [Rcp_Key],
    SELECT(
      BatchDetail[BchDtl_Rcp_ID],
      IN(
        [BchDtl_Bch_ID].[Bch_DateStamp],
        SELECT(
          Sales[Sls_TimeStamp],
          ([Sls_Key] = [_THISROW].[SlsDtl_Sls_ID])
        )
      ),
      TRUE
    )
  )
)

Trying to get the information from one table based on two conditions in another table