Jump to content
  • 0

Design Of History Table And Datasheet


Parma2015

Question

Hi!

I am designing a database where I want to keep track of assets (i.e an excavator) and the number of days it has been at a certain location (a project).

For this I have made a table with an autonumber Field (recno), an assetID and a FromDate.

I have also a Field for ToDate, but may be I can pull that date from when it is moved to the next project.

We have a project called "Available" to which an asset will be allocated whenever it is not at a certain project.

Soehow i have to either find a way to insert the ToDate into the record for that asset with the highest recno, or find a way to make a View to make up the number of days.

Then I wil calculate internal prices for internal invoicing.

I am sure there are some tricks to kepp track of any history like this.

Maybe I also want to put the latest location into my Asset Table, but I haven't figured it out yet how to insert a value into another table like this.

 

Regards

Jan H Arntzen  

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

Hi Jan;

 

Based on significant assumptions here is a possible solution:

 

tblAsset

assetID (system ID; not for human use)

assetName 

assetCode (your company's asset ID / serial number)

available (yes/no)

 

tblProject

projectID

projectName

projectAddress

 

tblAssignment (maybe this is you're recno?)

assignmentID

assetID

projectID

FromDate

ToDate

 

tblAssignment is a sort of linking table that links assets to projects but also describes when the asset was assigned.  This will be beneficial so you can have more than one asset at a project and for different times.

 

Create a view based on tblAssignment (return all) and tblAsset (return only matching).

Datediff(day, [@field:tblAssignment_FromDT],GetUTCDate())

Then create a Tabular Report and add that formula in a calculated field.  This will display the duration of an assignment.

 

Create two more DataPages; one for adding an asset to a project and one for removing an asset.

 

AssignAsset: Submission form: tblAssignment_AssetID and tblAssignment_ProjectID and a tblAssignment_FromDate.  Create an auto submitting form to set tblAsset_available to No. (http://forums.caspio.com/index.php/topic/4314-js-auto-submit-a-datapage/)

 

UnAssignAsset: Single Record Update: tblAssignment_ToDate then autosubmit tblAsset_available to Yes.

 

Hope this helps

Link to comment
Share on other sites

  • 0

Hi!

May be I didn't explain my intension with the "Available project was...

In the history table, I supposed that I will need only a field for the asset, one for the project, and one for the FromDate filled out.

The project "available" could rather be called "vacant", as a vacant machine (i.e an excavator) is allocated to that project when it is available for other projects (vacant).

Then, when the user changes the allocation on that asset from a projet to the "vacant" project, a new record is made in the history table with a new "FromDate". This date is at the same time the "ToDate" for the latest allocation, and the date value should be submitted to that record (unless this dateDiff can be calculated in a view, but I have seen that calculation in views are tricky?).

This way, an asset will always be allocated to a project. If not to an ordinar project, then to the "vacant" project.

Link to comment
Share on other sites

  • 0
Hi Jan,
 
Let me try to write how you can work with your application.
Of course, if I understand correctly, what is your goal.
 
Three tables:
 
The first table:
 
tblAsset
assetID (system ID; not for human use)
assetName 
assetCode (your company's asset ID / serial number)
 
Contains information about Assets. You have a Submission form to add new assets and a Report DataPage that allows to edit Assets on Details page.
 
The second table
 
tblProject
projectID
projectName
projectAddress
 
Contains information about Projects. One of projects is Vacant.
You have a Submission form to add new Projects and a Report DataPage that allows to edit Projects on Details page.
 
Users cannot delete neither Assets nor Projects.
 
And the third table:
 
tblAssignment 
assignmentID
assetID
projectID
FromDate
ToDate
DaysOnProject
 
You can create a View with these tables:
 
viewAssignment
First relation is
tblAssignment.assetID - tblAsset.assetID
tblAssignment.projectID - tblProject.projectID
 
Please make sure that tblAssignment is selected as an editable table.
 
* * *
 
Fill tblAsset and tblProject tables.
 
Create a Submission Form and add all Assets to the tblAssignment.
For example, the Submission Form can contains next fields:
assetID is a Dropdown, source is Lookup table; table is tblAsset; Field for display is assetName; Field for value is assetID.
projectID is a Hidden, on load assign Default Value - the ID of Vacant Project.
FromDate is a Hidden, on load assign System Parameter Timestamp.
 
When a new Asset appears, it should be added to the tblAssignment with this Submission Form.
 
* * *
 
Create one more Search and Report DataPages, that uses view viewAssignment as a DataSource and manages Assets.
 
On "Configure Search Fields" step, add
tblAssignment_ToDate field and set Comparison Type "Is Blank" (you can set Hidden Form Element too).
 
On the Result page, you can add fields:
tblAsset assetName
tblAssignment FromDate
tblProject projectName
 
On the Details page, you can add fields:
tblAsset assetName - Display Only
tblProject projectName - Display Only
tblAssignment FromDate - Display Only
Virtual Field 1 with label "Project" - Dropdown, source is Lookup table; table is tblProject; Field for display is projectName; Field for value is projectID; on exit pass [@projectID]
tblAssignment ToDate - Hidden; on exit pass [@FromDate]
tblAssignment DaysOnProject - Hidden
Virtual Field 2 - Hidden, on load assign Data Source Fields - tblAsset_assetID; on exit pass [@assetID]
 
Add the following code to the Footer of the Details page:
<script type= "text/javascript">
function getT() 
{
var prevValue='[@field:tblAssignment_projectID]';
if(document.getElementById("cbParamVirtual1").value != prevValue)
 {
   document.getElementById("EditRecordtblAssignment_ToDate").value = '[@cb:Timestamp]';
   var v_sDate = '[@field:tblAssignment_FromDate]';
   var v_eDate = '[@cb:Timestamp]';
   var sDate = new Date(v_sDate);
   var eDate = new Date(v_eDate);
   var diff = new Date(eDate.getTime() - sDate.getTime());
   var diffDays = diff.getUTCDate() - 1;
   document.getElementById("EditRecordtblAssignment_DaysOnProject").value = diffDays;
  }
else
return false;
}
document.getElementById("caspioform").onsubmit = getT;
</script>

On the "Destination and Triggers" step of the Manage DataPage, select "Go to a new page" and enter the page, where the following Submission Form is deployed:

 
This Submission Form uses the tblAssignment table as DataSource;
Three fields:
assetID On load Receive value or parameter: External Parameter [@assetID]
projectID On load Receive value or parameter: External Parameter [@projectID]
FromDate On load Receive value or parameter: External Parameter [@FromDate]
 
Add to Header the following code: 
<div align="center"> <p>Processing...</p> </div>

Add to the Footer the following code:

<script type="text/javascript">
  if(document.getElementById("caspioform")) {
  document.getElementById("caspioform").style.display = 'none';
  setTimeout('document.forms["caspioform"].submit()',1000); } 
</script>

Now, when a user changes the Project, "ToDate" and "DaysOnProject" are recorded; then AutoSubmission Form creates a new Assignment.

 
* * *
 
You can create one more Search and Report DataPage, that uses view viewAssignment as a DataSource and allows to see History of Assets.
 
I hope, it helps :)
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...