Turning computed/relation/lookup data into Basic column data

Ask for Help
1

Tables…

  • Services
  • Invoices
  • Ledger Entries

Currently, when i want to add an item to an invoice, the Item ID and Invoice ID that the item should be added too get placed into the Ledger Entries table. In the Ledger Entries table it looks up price, name, pay, among other things.

This works fine, but when we change prices it automatically adjusts all invoices. Which causes a bunch of issues.

After rewatching a video (with more experience now) from Robert Pettito that uses a form table detail screen to have just 1 form instead of XX, im going to try it. Video is found here (https://www.youtube.com/watch?v=Dr1pCuL2Mg0&list=PLiXRc87c6KKUcnZHIcfG3B1fgGbsOO0zw&index=4&ab_channel=RobertPetitto) This will clean up my app massively. I eventually had so many different forms depending on navigation to get to the form that when i made a change to approach it was so hard to update everything.

So… im starting fresh with all the lessons ive learned along the way (for the millionth time… but each time it takes me 2-3 hours to recreate what took a week while learning!)

I’m wondering if it would be better to have all the lookups on the newly made Ledger Entry FORM table, and if possible have it write that data into basic columns on the Ledger Entry Table.

Is there a better way of going about this?

2

What you could do is keep a “Price History” table. It would have columns such as:

  • Date
  • ItemID
  • Old Price
  • New Price

Then you could look up the Price based on the Ledger Date. Could also be useful to have a price history for each item? :man_shrugging:

That would be a simple approach, yes.

1 Like
3

That makes sense, I hadn’t considered a price history or the value of it.

Thanks, I am going to have a think and maybe try both to see what it looks like.