My Visual Database — Need help with adding calculated field or may be adding extra column?

Re: Need help with adding calculated field or may be adding extra column?

null@example.com (kavinacomputers) — Wed, 08 May 2024 10:13:53 +0000

thank you @derek I am still learning from your small script.

For now not implementing cascade delete - but created example and tested it how it works.

Refining whole project again. and working on reports designing.

Re: Need help with adding calculated field or may be adding extra column?

null@example.com (derek) — Mon, 06 May 2024 17:48:56 +0000

Hi,
I'm not sure if I understand all your points correctly but hopefully the following will make sense.
1.
I was looking at your data schema and would suggest instead of using a flag (is_inward = yes), you could just use a positive quantity and for is_inward = no, just use a negative quantity.
Personally I think this would be less confusing (and is one less thing for the user to input).
2.
The stock balance is already being calculated so Instead of 'is_open = no', just replace this with an automatic check whether 'stock balance = 0'. You could also add a flag using a calculated field to show, for example if a job is 'not started', 'in progress' or 'finished' to make it even more obvious.
3.
With regard to your question about parent-child tablegrids, have a look at the attachment; form1.tablegrid2 and form1.tablegrid3 are both 'child' tablegrids of form1.tablegrid1. Click in any row of tablegrid1 to see the associated 'child' transactions. I've chosen to split 'inwards' and 'outwards' transactions into two child tables but it's up to you how you best want to see the information.
4.
Cascade / delete is theoretically a good idea if you want to keep your data tidy.. However, if you have a lot of tables with relationships, you can end up deleting data that you might need without realising it.
But more importantly, if your application is to be used commercially, there might be legal requirements (depending on your country) to maintain all data and not delete anything.
If you're considering using cascade / delete from a business process point of view because, for example, there is a product that you no longer use, it's perhaps a better idea to add a 'do not use' flag to the appropriate table so that it can be automatically excluded from any subsequent transactions..
5. Concerning your point about multiple transactions on the same day, you might want to consider re-defining your transaction date from a field type of 'date' to a field type of 'date/time'.
.
The attached project hopefully clarifies some of the above points. Also, perhaps it gives you some ideas of how your project might develop once you get more familiar with MVD. As always with MVD there are lots of different ways to do things and this isn't to say that one way is correct and another way is incorrect - it's more just to show you options.
There is a (relatively) small script attached to the project but this is more to do with a) the 'look and feel' and b) to add some validation rather than the basic functionality which can be done using standard MVD.
If anything is not clear, just shout.
Derek.

Re: Need help with adding calculated field or may be adding extra column?

null@example.com (kavinacomputers) — Sun, 05 May 2024 04:19:44 +0000

Logic behind using is_inward and is_open

Example
client gives 25 rolls of fabric total 25000 meters for printing -
inward transaction is_inward = yes and is_ open=yes for transections.

2 rolls printed today and given back to client total meters 2000.
outward transection is_inward = no and is_open=yes stock is available open for transections.

23 rolls printed another days and 23000 meters given back to client.
outward transection is_inward = no and is_open=no - no stock left to do transections.

tra_close will show that closed inward and outward - stock came - printed - outward done. task finished
at this point if both flag is no - outward not allowed

hope I explained properly

@derek

I have few more qustions

1) when I was reading book dgs filter catalogue page 46

there was a concept called parent-child in tablegrid

is that good idea to incorporate in project also what about cascade delete.

2) same example as above

Supplier/Client (MAIN parent) gives bill (one to many relationship)
add bill no(child(parent of bill multiple entries))
multiple bill entries(child(parent of transections).

cascade delete will be good idea here?

3)I am confuse here how to go about it?

multiple outward can happen in one day or multiple time in month. how to achieve this
d-0001 - 2024-05-04
d-0002 - 2024-05-04
...................
.....
d-000n - 2024-05-04

same client merge all bills.
m-0001 - bill for May month. mention all the entries done. In month and all the total amount and taxes.

I will track q_out and q_in as per your suggestion.

Re: Need help with adding calculated field or may be adding extra column?

null@example.com (derek) — Sat, 04 May 2024 15:13:51 +0000

Hi,
Looking at the screen shot in your attachment, I would use calculated fields to display the 'in', 'out' and 'stock in hand' balances per product for each supplier (please see attached as an example of how to create your calculated fields).
I haven't added a calculated field for 'tra close' because I can't work out from your attachment what this is but the principle, in terms of indicating it with a calculated field, is the same as for the other calculated fields.
I have also added two calculated fields on your 'transactions' table to show 'quantity in' and 'quantity out' rather than just using one table field with an indicator as you are currently doing. Moving forward, you might consider creating discrete fields ('quantity in', 'quantity out') as this is perhaps more user-friendly than having just one field ('quantity'_ and using an indicator ('is_inward'). But these are just suggestions.
Regards,
Derek.

Need help with adding calculated field or may be adding extra column?

null@example.com (kavinacomputers) — Sat, 04 May 2024 04:39:43 +0000

Not sure on this if there is a way to do add column
Or calculated field.

Working with this very small project and learning.

attached project is iteration of 4th revision. and now I am doing chunks only. and lot of normalization.
from 16 tables to 5 tables. (that was fun - 32 forms)

I need help with populating column.

image is in zip file what I am trying to do.

Thanks in advance.