Data Tables Join

Feature Requests
#1

The ability to join data tables based upon criteria passed in. Linked tables/rows don’t work for all situations. In this case I’m trying to determine the price of a fabric (table A) but the price (table B) is contingent upon other factors (store, current date, etc.) than what’s in the fabric table alone (thus I can’t just link the rows).

1 Like
Linked rows question
#2

Hi there,

In case it helps:

  • on a dedicated plan you can use SQL to access your Anvil data tables.

  • you may be interested in the Anvil query operator which allows for very powerful query statements

I’ve tried to understand the example you wrote above, but there is not enough detail (for me anyway) to determine if there is a way to accomplish what you are attempting using Anvil data table links. If you have an example app to share and a more detailed description, it may be possible for us to help you figure out any immediate roadblocks.

#3

Thanks for the quick reply!

The Dedicated Plan costs 13X the Standard plan–that’s not really a financially feasible option at this time.

The query operator is helpful but still doesn’t allow me to join tables together that aren’t already linked.

I can’t share a project but I’ll try to give more insight into the data structure. The tables involved are:
Pricing:
[{‘name’: ‘Attribute’, ‘type’: ‘string’},
{‘name’: ‘AttributeValue’, ‘type’: ‘string’},
{‘name’: ‘Cost’, ‘type’: ‘number’},
{‘name’: ‘PriceList’, ‘type’: ‘liveObject’}, #Linked to PriceList table
{‘name’: ‘Product’, ‘type’: ‘string’}, #Linked to Product table
{‘name’: ‘Retail’, ‘type’: ‘number’},
{‘name’: ‘ValidFrom’, ‘type’: ‘datetime’},
{‘name’: ‘ValidTo’, ‘type’: ‘datetime’}]

Stores:
[{‘name’: ‘BillTo’, ‘type’: ‘liveObject’},
{‘name’: ‘Code’, ‘type’: ‘string’},
{‘name’: ‘Name’, ‘type’: ‘string’},
{‘name’: ‘PriceList’, ‘type’: ‘liveObject’}, # Linked to PriceList table
{‘name’: ‘PricingPref’, ‘type’: ‘string’},
{‘name’: ‘ShipTo’, ‘type’: ‘liveObject’},
{‘name’: ‘StoreGroup’, ‘type’: ‘liveObject’}] #Linked to StoreGroup table

Fabrics:
[{‘name’: ‘Code’, ‘type’: ‘string’},
{‘name’: ‘Content’, ‘type’: ‘string’},
{‘name’: ‘FactoryCode’, ‘type’: ‘string’},
{‘name’: ‘Name’, ‘type’: ‘string’},
{‘name’: ‘Product’, ‘type’: ‘string’},
{‘name’: ‘Supplier’, ‘type’: ‘string’},
{‘name’: ‘Tier’, ‘type’: ‘string’},
{‘name’: ‘Type’, ‘type’: ‘string’},
{‘name’: ‘ValidFrom’, ‘type’: ‘datetime’},
{‘name’: ‘ValidTo’, ‘type’: ‘datetime’}]

The ideal query would determine which pricelist the store belongs to, select the relevant rows/columns from the pricing table (based on PriceList, ValidFrom date, ValidTo date) and join that with the fabric table based upon the Tier column (joining on AttributeValue in the pricing table). I’m trying to recreate some of the flexibility in SAP’s ERP pricing engine but don’t really want to have to use a bunch of dictionaries and loops to pull all of the functionality that I need together.

Fabric Tier can’t link directly to the pricing table as Fabrics aren’t tied directly to pricelists (and shouldn’t be to minimize data maintenance in the system).

Thank you,

Jeff

1 Like
#4

Thanks for the details. I was hoping for a simple example application (with fake data) where your tables are set up to demonstrate the issue. I’ll look at the details above if I have some time.

Also, did you know that on any plan you can access common external databases? Please see here for more details:
https://anvil.works/docs/data-tables/external-database

#5

That’s still not completely clear (to me).
If it was SQL, what were you trying to accomplish?

SELECT F.*
FROM FABRIC F, STORES S, PRICING P
WHERE
    S.CODE = my_store_code
and S.PriceList = P.PriceList
and my_date BETWEEN F.ValidFrom and F.ValidTo
and F.Tier = P.AttributeValue

?

#6

Hi Aldo,

Yes, that’s basically what I’m looking for with some additional WHERE criteria added in and only returning F.* and P.Cost, P.Retail. Is there a better way to attack this?

Thank you,

Jeff

#7

Hi @jeff

To check I’ve understood the question, I think what you’re describing could be achieved by this code:

prices = app_tables.Pricing.search(
  PriceList=store['PriceList']
)

products = []
for p in prices:
  products.append(
     'pricing': p,
     'fabrics': app_tables.Fabrics.search(Tier=p['AttributeValue'])
  )

If you prefer a ‘one-liner’, that could be:

products = [
  {
    "pricing": p
    "fabrics": app_tables.Fabrics.search(Tier=p['AttributeValue'])
  } 
  for p in app_tables.Pricing.search(PriceList=store['PriceList'])
]

You can optimise this by loading the fabrics into memory:

fabrics_by_tier = {f['Tier']:f for f in app_tables.Fabrics.search()}

Then you can look the fabrics up from this dictionary:

products = [
  {
    "pricing": p
    "fabrics": fabrics_by_tier[(p['AttributeValue']]
  } 
  for p in app_tables.Pricing.search(PriceList=store['PriceList'])
]

Does that work for you?

3 Likes
#8

Hi Shaun,

Thank you for the great response! Flip the tables around and that’s exactly what I need to do.

Thanks again!

Jeff

1 Like
#9

This would be incredibly helpful as a feature.