0

Relations between records in the same table

Hi, I have a table for Invoices, where the type of the invoice can be selected: Fee requester, Proforma invoice, Invoice, Modified invoice, Storno invoice.

The layout of these documents are the same, so the fields are:
- invoice type
- invoice ID
- invoice number
- seller
- amount of money
- currency
- etc.

The "workflow" of the invoicing in my country is the following:
- Fee requester (has a unique invoice number; not mandatory)
- Proforma invoice (has a unique invoice number; only mandatory, if a fee requester is paid)
- Invoice (has a unique invoice number; mandatory, if the Proforma invoice doesn't contain the full price of the purchase, e.g. when I prepay 50% of the goods)
- Modified invoice (has a unique invoice number; not mandatory)
- Storno invoice (has a unique invoice number; only mandatory if an invoice is needed to be reversed)

A use case:
I get a fee requester for the 50% of a purchase on monday, I make a redocord for it. I pay that on wednesday and receive on the same day the Proforma invoice about what I have paid, I make another record for that. Two weeks later I receive the goods and an Invoice about the full price deducted by the Proforma invoice's amount, I make a third record for that. I see that my company data is wrong on the Invoice, they send me a Modified invoice with the corrected company data, and I make a fourth record for that. When I open up the box it turns out that they didn't send me the product I actually wanted, and can't provide that, so the business fails -> they send me a Storno invoice, and I make another record for that (and send the wrong goods back).

What do I want to do?
- When I create a Proforma invoice, I want to connect that to its Fee requester's record to see the history. A Fee requester can be connected to one Proforma invoice and many Fee requester can be connected to one Proforma invoice.
- When I create an Invoice, I want to connect that to its Proforma invoice's record. Same relation as above.
- When I create a Storno invoice I want to connect that to its Invoice's record which is reversed. Also the same algorithm with the Modified invoice. 1:1 relation.

What do I want to see?
When I look at a record I want to see the predecessor and successor invoices and based on this I could make listings of expected Proforma invoices which I must receive within a certain period of time, or when I get a Storno invoice I want to see whether it was already paid based on a Fee requester or Invoice or not, so on.

My problem is that I cannot set up the correct relations for connecting/linking the Invoices together. It would be very handy If I could keep all these invoice types in one table, by the way child tables didn't help me neither when I tried.

Short story long, this is what I want to apply, but can't figure out how. Thanks in advance!

3 replies

null
    • Medgyesy_Andras
    • 3 wk ago
    • Reported - view

    And the next issue will be something like 1:1 relation:

    1. I have the Invoices table
    2. I have the Bank transfer table (I receive the transaction data from my bank via API)
    Problem: how can I link one Invoice record with one Bank transfer record?

      • John_Halls
      • 3 wk ago
      • Reported - view

       I think you have a valid case for a one to one relationship here. I have written a reply here.

      Regards John

    • Fred
    • 3 wk ago
    • Reported - view

    Since all of the data is the same it would make sense to have one table. I have made a test DB to show how it could look.

    Open the OneTable table to see how it works. I've added a simple choice field called Type and that is how you tell them apart.

    One thing you can do is create reference fields to the table itself. Kinda weird but it is possible. When you do that it creates two reference fields the 1 side and the M (many) side. Now you just have to rename it and then show/hide them depending on the type of invoice.

    I'm sure I've gotten the directions wrong somewhere.

    You will see that I've made a child table called FeeRequesters to OneTable. I made another table called Clients that is also linked to FeeRequesters, which then makes FeeRequesters a many to many table. One client can be linked to an Invoice or many Invoices. Many clients can be linked to one Invoice or many Invoices.

Content aside

  • 3 wk agoLast active
  • 3Replies
  • 38Views
  • 3 Following